Rhea’s Handbook

A self-contained reference for Power BI & data engineering interviews and the job itself. Read top-to-bottom once, then use it like a wiki. Pair it with the question bank (drill) and the AI tutor (practice out loud).

★ Specialized handbooks

Deep, standalone references beyond Power BI — each a full end-to-end guide on the same shared design. Built progressively; live ones are linked.

01 Power BI fundamentals

Power BI is Microsoft’s analytics platform. Data flows through four stages: Connect → Transform → Model → Visualize, then publish and share.

The pieces

The two engines under the hood

• Power Query (M language) — the ETL layer. Cleans and shapes data before it loads.
• VertiPaq + DAX — the in-memory columnar model and the calculation language that runs over it at query time.

Report vs dashboard vs app

• Report: multi-page, interactive, built in Desktop, bound to one dataset.
• Dashboard: Service-only single canvas of pinned tiles (can span datasets). Executive at-a-glance.
• App: packaged, governed bundle of reports/dashboards distributed to an audience.

Connectivity modes

02 Power Query / M

Power Query is the ETL layer. Every action records a step in the Applied Steps list, building a repeatable, refreshable pipeline written in the M language.

Golden rules

• Do row-level cleanup here, not in DAX: split/merge columns, change types, remove rows, unpivot, merge/append.
• Set data types early — correct types compress better and prevent errors downstream.
• Preserve query folding (see Performance) — keep foldable steps first.
• Unpivot wide data into long/tidy format so new columns (e.g. months) need no rework.

Common transforms

A custom M function

fxClean = (src as table) as table =>
  let
    promoted = Table.PromoteHeaders(src),
    typed = Table.TransformColumnTypes(promoted,
              {{"Date", type date},{"Amount", type number}}),
    clean = Table.SelectRows(typed, each [Amount] <> null)
  in clean

03 Data modeling

The model is the foundation — good DAX and performance depend on it. Aim for a star schema.

Star schema

• Fact table: business events. Numeric measures + foreign keys. Long & narrow, high row count. (Sales, Orders)
• Dimension tables: descriptive attributes you slice by. (Date, Product, Customer, Region)
• Relationships: dimension 1 → many fact, single filter direction.

Always build a Date dimension

Time-intelligence DAX needs a contiguous date table (one row per day). Create it, relate it to the fact’s date, and Mark as Date Table. Add Year/Quarter/Month/Fiscal columns.

Date =
ADDCOLUMNS(
  CALENDAR( DATE(2020,1,1), DATE(2030,12,31) ),
  "Year", YEAR([Date]),
  "Month", FORMAT([Date],"MMM"),
  "MonthNo", MONTH([Date]),
  "Quarter", "Q" & FORMAT([Date],"Q") )

Relationships & pitfalls

• Cardinality: 1:many is normal. Many:many is a smell — use a bridge table.
• Cross-filter: keep single direction; bidirectional risks ambiguity, slow queries, RLS leaks.
• Role-playing dim (Order Date / Ship Date): one active relationship + inactive ones activated by USERELATIONSHIP.
• SCD-2 (slowly changing dimension): keep history with ValidFrom/ValidTo + surrogate keys (handled upstream).
• Use slim integer surrogate keys for relationships (compress well, fast joins).

04 DAX foundations

DAX (Data Analysis Expressions) is the calculation language. Master three ideas and the rest follows: calculated columns vs measures, evaluation contexts, and CALCULATE.

Column vs measure

The two evaluation contexts

• Row context: "the current row." Exists in calculated columns and inside iterators (SUMX, FILTER).
• Filter context: the set of filters applied by slicers, rows/columns of a visual, and CALCULATE. Measures evaluate here.
• Context transition: CALCULATE (or a measure) inside a row context turns that row into a filter context. Powerful and a common source of bugs.

CALCULATE — the keystone

The only function that directly modifies filter context. Filter arguments add, replace, or remove filters.

Sales LY =
CALCULATE( [Total Sales], SAMEPERIODLASTYEAR('Date'[Date]) )

Variables

YoY % =
VAR Cur = [Total Sales]
VAR Prev = CALCULATE([Total Sales], DATEADD('Date'[Date],-1,YEAR))
RETURN DIVIDE(Cur - Prev, Prev)

VAR evaluates once, improves readability, and avoids context surprises.

05 DAX function reference

The functions you actually use, grouped. Know these cold.

Aggregation

Filter & context

Relationships

Time intelligence

Logic, text, misc

06 DAX patterns & recipes

Copy-ready solutions to the problems that come up daily and in interviews.

% of total (respecting slicers)

% of Total =
DIVIDE( [Sales],
  CALCULATE([Sales], ALLSELECTED(Product[Category])) )

Year-over-year growth

YoY % =
VAR P = CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN DIVIDE([Sales]-P, P)

3-month rolling average

Sales 3M Avg =
VAR W = DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -3, MONTH)
RETURN DIVIDE(
  CALCULATE([Sales], W),
  CALCULATE(DISTINCTCOUNT('Date'[YearMonth]), W) )

Ranking

Product Rank =
RANKX( ALL(Product[Name]), [Sales], , DESC, Dense )

Dynamic title / measure selection

KPI Label =
SWITCH( TRUE(),
  [Sales] > 1000000, "On track",
  [Sales] > 500000,  "Watch",
  "Below target" )

Count of items above a threshold (correct total)

Customers > 10k =
SUMX( VALUES(Customer[Key]),
  IF([Sales] > 10000, 1, 0) )

07 Performance tuning

Diagnose

• Performance Analyzer (Desktop): record → see DAX query time vs visual render per visual.
• DAX Studio: run the slow query, read Server Timings (SE vs FE), spot CallbackDataID (SE calling FE per row = slow).
• VertiPaq Analyzer: size per column — find the cardinality hogs.

The two engines

• Storage Engine (SE): VertiPaq, multi-threaded, cached, fast scans. Healthy queries are mostly SE.
• Formula Engine (FE): single-threaded, no cache, complex logic. Too much FE = slow.

Fix checklist

1. Reduce cardinality: split DateTime into Date+Time, drop GUIDs/precise timestamps, trim unused columns.
2. Star schema, single-direction relationships, integer keys.
3. Replace heavy calculated columns with measures or Power Query steps.
4. Avoid bidirectional filters and giant iterators over the fact.
5. Incremental refresh for big facts (needs query folding).
6. Aggregation tables for huge/DirectQuery facts (agg-awareness hits the small table first).
7. Disable Auto Date/Time (hidden date tables bloat the model).
8. Fewer visuals per page; avoid high-cardinality slicers.

Query folding

Power Query translating steps into native source SQL. The DB does the work; only results return. Right-click step → View Native Query to check. Folding-breakers (Table.Buffer, Index, some custom M) stop it — keep foldable steps first.

08 Security / RLS

Row-Level Security

Define roles with a DAX table filter in Desktop; assign users/groups in the Service.

-- Dynamic RLS: one role for everyone
[Region] = LOOKUPVALUE(
  UserMap[Region], UserMap[Email], USERPRINCIPALNAME() )

Static vs dynamic

• Static: one role per segment, hardcoded filter. Simple, doesn’t scale.
• Dynamic: one role, filter driven by USERPRINCIPALNAME() + a mapping table. Scales to thousands.
• Hierarchy RLS: PATH + PATHCONTAINS so a manager sees their whole subtree.

Object-Level Security (OLS)

Hides entire tables/columns from a role (set in Tabular Editor). RLS filters rows; OLS hides structure.

09 Visualization

Pick the visual by the question

Interactivity features

• Slicers (on-canvas filters), filter pane (visual/page/report/drill-through scopes).
• Drill-down (hierarchy in one visual) vs drill-through (to a detail page).
• Bookmarks + Selection pane: toggles, reset buttons, guided stories.
• Field parameters: let users swap the measure/dimension on a visual.
• Tooltips (incl. report-page tooltips), conditional formatting, Q&A.

Design principles

• Start from the decision the user must make. Fewer, clearer visuals win.
• Consistent color with meaning; accessible contrast; clear titles.
• Most-important top-left (eyes scan Z-pattern). Whitespace is not wasted space.

10 Deployment & ops

• Workspaces: separate dataset (semantic model) workspaces from report workspaces in big orgs.
• Shared/certified datasets: one model, many reports connect live — no duplicated logic.
• Apps: package & distribute to an audience (don’t use raw share links broadly).
• Gateway: standard mode for shared enterprise refresh of on-prem sources.
• Scheduled / incremental refresh: set in the Service; re-enter creds when they expire.
• Deployment Pipelines: Dev → Test → Prod with parameter rules per stage.
• Version control: save as .pbip (text) for Git; Tabular Editor + ALM Toolkit for model diffs.
• Licensing: Pro per user, or Premium/Fabric capacity so Free users can view.

11 SQL for BI

Every BI/data role tests SQL. Know these patterns.

Execution order

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. WHERE filters rows; HAVING filters groups.

Joins

Window functions

ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC)   -- unique
RANK() / DENSE_RANK()                                  -- ties
LAG() / LEAD()                                       -- prev/next row
SUM(x) OVER(ORDER BY d ROWS UNBOUNDED PRECEDING)     -- running total

Must-know queries

• Top-N per group → DENSE_RANK in a subquery, filter rnk <= N.
• Nth highest → rank = N, or correlated MAX subquery.
• Duplicates → GROUP BY ... HAVING COUNT(*) > 1.
• MoM growth → LAG + NULLIF.
• In-A-not-B → NOT EXISTS / LEFT JOIN IS NULL / EXCEPT.
• Pivot → SUM(CASE WHEN ... THEN ... END).

DDL/DML

DELETE (rows, logged, rollback) vs TRUNCATE (all rows, fast, resets identity) vs DROP (whole object). PK (unique, not null) / FK (referential integrity) / surrogate key (system integer).

12 Data engineering essentials

BI sits on top of data engineering. Know the layer that feeds your models.

ETL vs ELT

• ETL: Extract → Transform (in a staging engine) → Load. Classic, for on-prem warehouses.
• ELT: Extract → Load raw → Transform in the warehouse/lake. Modern cloud (Snowflake, BigQuery, Fabric) — cheap storage + powerful compute.

Data warehouse modeling (Kimball)

• Dimensional modeling: facts + conformed dimensions, star schema.
• Grain: define the level of one fact row first (one row per order line).
• SCD types: Type 1 overwrite, Type 2 add a versioned row (ValidFrom/To + surrogate key), Type 3 add a column.
• Conformed dimensions: shared dims (Date, Customer) used across facts — the only safe way facts relate.

The medallion (lakehouse) architecture

Pipelines & orchestration

• Ingestion: batch vs streaming; CDC (change data capture) for incremental.
• Orchestration: Azure Data Factory / Synapse pipelines, Airflow (DAGs), Fabric Data Factory.
• Idempotency: re-running a load shouldn’t duplicate data (use MERGE/upsert + watermarks).
• Data quality: validate row counts, nulls, referential integrity, schema drift; fail loud.

Big data / Delta

• Spark: distributed compute for large transforms (Databricks, Fabric notebooks).
• Delta Lake / Parquet: columnar files with ACID transactions, time travel, schema enforcement.
• Partitioning: split big tables by date/key so queries prune to relevant files.

13 Microsoft Fabric

Microsoft’s unified SaaS analytics platform — everything on OneLake (one logical data lake, Delta format).

DirectLake (the big one)

Reads Delta/Parquet from OneLake directly into VertiPaq — Import-like speed, DirectQuery-like freshness, no scheduled refresh. Falls back to DirectQuery on the SQL endpoint if a query exceeds limits. Wins when data already lands as Delta in a Lakehouse.

14 Statistics for analysts

• Mean vs median: median for skewed/outlier data (income, latency); mean for symmetric data feeding further math.
• Percentiles (p95/p99): averages hide the tail; SLAs use percentiles. DAX PERCENTILE.INC, SQL PERCENTILE_CONT.
• Std deviation / variance: spread of data; flag outliers via z-score (>3σ).
• Correlation ≠ causation: dashboards show association; recommend A/B tests for cause; watch confounders.
• Distributions & skew: a big mean-median gap signals skew — choose aggregation accordingly.
• Investigating a spike: validate data first (ETL artifact?), decompose by segment, check denominator & seasonality, then hypothesize.

15 PL-300 certification roadmap

PL-300 (Microsoft Power BI Data Analyst) is the credential Indian recruiters recognize. Four skill areas:

Study plan (4–6 weeks)

1. Chapters 1–3 here (Power Query, modeling) — build a project end to end.
2. Chapters 4–6 (DAX) — write every pattern yourself.
3. Chapters 7–10 (performance, RLS, viz, deploy).
4. Microsoft Learn PL-300 path + 2–3 practice exams. Aim 80%+ before booking.

Next steps after PL-300: DP-600 (Fabric Analytics Engineer), DP-203/DP-700 (data engineering).

16 Resume & interview day

Resume that passes the screen

• Lead each project with impact + numbers: "Cut refresh 2h→15m", "150 daily users", "saved 10 hrs/week".
• List the real stack: Power BI, DAX, Power Query, SQL, Azure/Fabric, gateway, RLS.
• Keep it 1–2 pages; put a short skills summary up top for ATS keywords.
• Be ready to defend every line — only claim what you can explain.

The day before

• Re-read your projects; prepare 3–4 STAR stories (conflict, challenge, failure, impact).
• Practice "tell me about yourself" out loud (use the AI tutor).
• Revise: CALCULATE, contexts, star schema, RLS, performance, top SQL queries.
• Know your numbers: current CTC, expected range, notice period.

During

• Think aloud on scenarios — they grade your reasoning, not just the answer.
• If unsure, say how you’d find out. Never bluff a fabricated fact.
• Tie technical choices to business value.
• Always ask 2–3 thoughtful questions at the end.

17 Glossary