Power BI

Intermediate to Advanced — Data Modeling, DAX & Performance, Explained with Examples

Star Schema · DAX Context · Power Query · RLS · Performance Tuning · Interview-Ready

A practical, example-driven study guide for Data Engineering / BI interviews

 Table of Contents

1. Power BI Architecture & Data Flow Overview

2. Data Modeling: Star Schema, Relationships & Cardinality
3. Power Query (M) — Transformation Deep Dive
4. DAX Fundamentals: Calculated Columns vs Measures vs Tables
5. DAX Context: Row Context vs Filter Context
6. CALCULATE, Time Intelligence & Iterator Functions
7. Advanced DAX Patterns (Interview Favorites)
8. Performance Optimization: VertiPaq, Query Folding, Aggregations
9. Row-Level & Object-Level Security
10. Power BI Service: Datasets, Dataflows, Gateways, Deployment
11. Incremental Refresh & Large Datasets
12. Composite Models: Import vs DirectQuery vs Dual
13. Integration with the Data Engineering Stack
14. Common Interview Questions — Fully Answered
15. DAX Practice Problems (Solve These Yourself)
16. Quick-Reference Cheat Sheet
 1. Power BI Architecture & Data Flow Overview INTERMEDIATE

Interviewers for data engineering roles that touch Power BI care less about clicking through the UI and more about
how data actually flows and gets stored — this section sets up the vocabulary everything else builds on.

1.1 The Three Layers

Layer Tool Job

Get & Transform Power Query (M language) Extract, clean, and shape data before it loads into the model

Tabular model (VertiPaq Stores tables, relationships, and DAX measures in an in-memory
Data Model
engine) columnar store

Report/Visual
Report canvas Visuals that query the model and render results
Layer

A critical mental model: Power Query runs before data lands in the model (transform-then-load, mostly), while DAX
measures are evaluated at query time, every time a visual refreshes on screen. Confusing these two stages is one of
the most common beginner-to-intermediate mistakes interviewers probe for.

1.2 Power BI Desktop vs Power BI Service

Power BI Desktop — the authoring tool: build the model, write DAX, design reports.
Power BI Service ([Link]) — the cloud platform: publish, schedule refreshes, share, manage
security, host dataflows and deployment pipelines.

1.3 The VertiPaq Engine in One Paragraph

Imported data is stored column-by-column, heavily compressed with dictionary encoding and run-length encoding,
entirely in memory. This is why Import mode is so fast — it's the same columnar philosophy as Parquet in a data
lake, but held in RAM with its own compression engine rather than on disk.
 2. Data Modeling: Star Schema, Relationships & Cardinality
INTERMEDIATE

2.1 Why Star Schema Is Still the Right Answer

A central fact table (transactions, events — numeric, high row count) connected to surrounding dimension
tables (customer, product, date — descriptive, low row count) minimizes join complexity and plays to VertiPaq's
strengths. A snowflake schema (dimensions further normalized into sub-dimensions) adds unnecessary
relationship hops and typically hurts both performance and DAX simplicity in Power BI specifically.

-- Typical star schema for a sales model

FactSales (order_id, date_key, customer_key, product_key, quantity, amount)
DimDate (date_key, date, year, month, quarter, is_weekend)
DimCustomer(customer_key, customer_name, segment, region)
DimProduct (product_key, product_name, category, subcategory)

2.2 Relationship Cardinality

Cardinality Typical Use

One-to-many (1:*) DimDate to FactSales — the standard, expected relationship direction

Many-to-many (*:*) e.g. a bridge table for accounts shared by multiple customers — use sparingly, adds ambiguity

One-to-one (1:1) Rare; usually signals two tables that should be merged into one

2.3 Filter Direction — Single vs Both

Single-directional (dimension → fact) is the default and recommended setting: filtering a dimension filters the
fact table, but not the reverse. Bidirectional filtering lets the fact table also filter dimensions (needed for some
many-to-many scenarios) but can introduce ambiguous filter paths and circular logic in larger models — use it
deliberately, not as a default fix for a broken visual.

Interview tip: "When would you use bidirectional filtering, and what's the risk?" — expected answer: needed for
many-to-many bridge tables or certain slicer-on-fact-table scenarios, but the risk is ambiguous/circular filter
propagation and a measurable performance cost, so it should be scoped to the specific relationship that needs it, not
applied model-wide.

2.4 The Date Table

DimDate =
ADDCOLUMNS(
CALENDAR(DATE(2020,1,1), DATE(2027,12,31)),
"Year", YEAR([Date]),
"MonthNumber", MONTH([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"Quarter", "Q" & FORMAT([Date], "Q"),
"IsWeekend", WEEKDAY([Date], 2) > 5
)

A dedicated, marked Date table (Model view → Mark as Date Table) is required for DAX time-intelligence functions
( TOTALYTD , SAMEPERIODLASTYEAR , etc.) to work correctly — a very commonly asked setup detail.
 3. Power Query (M) — Transformation Deep Dive INTERMEDIATE

3.1 The Applied Steps Model

Every UI action in Power Query generates a step in the M language, chained together as a pipeline — conceptually
identical to a sequence of chained PySpark transformations, just expressed as M code instead.

let
Source = [Link]("[Link]", "SalesDB"),
dbo_Orders = Source{[Schema="dbo",Item="Orders"]}[Data],
FilteredRows = [Link](dbo_Orders, each [Amount] > 0),
RenamedColumns = [Link](FilteredRows, {{"CustID", "CustomerID"}}),
AddedCustomColumn = [Link](RenamedColumns, "OrderYear", each [Link]([OrderDate]))
in
AddedCustomColumn

3.2 Query Folding — The Most Important Power Query Performance Concept
When the source is a database, Power Query tries to translate each applied step back into a single native query
(SQL) pushed down to the source, instead of pulling all rows into Power BI and filtering locally. This is query
folding. Steps like filtering, renaming, and simple type changes typically fold; steps like custom M functions,
merging with a non-foldable source, or certain text transformations break folding.

# Right-click any step -> "View Native Query" to confirm folding is active.
# If greyed out, folding has stopped at (or before) that step.

Interview tip: "Your refresh suddenly got much slower after adding a transformation — what would you check?"
Expected answer: check whether that step broke query folding, and if so, reorder steps so foldable operations (filters,
column selection) happen before the non-foldable one, minimizing the amount of data pulled unfolded.

3.3 Merge vs Append

Operation SQL Equivalent Use

Merge Queries JOIN Combine columns from two tables based on a key

Append Queries UNION ALL Stack rows from multiple tables with the same structure

3.4 Parameters for Reusable, Environment-Aware Queries

let
ServerName = ServerNameParam, // an M parameter, e.g. switch between dev/prod
Source = [Link](ServerName, "SalesDB")
in
Source

3.5 Handling Errors in Power Query

= [Link](Source, "SafeDivision", each

try [Revenue] / [Units] otherwise null
)

// Replace all errors in a column with a default

= [Link](Source, {{"Revenue", 0}})
4. DAX Fundamentals: Calculated Columns vs Measures vs Tables
INTERMEDIATE

4.1 The Three DAX Object Types

Type Computed Stored? Use When

Calculated Yes — takes up Value needed for slicing/filtering/relationships (e.g.

Row by row, at refresh time
Column model memory a category flag)

Dynamically, at query time, per No — computed on Aggregations that must respond to filters/slicers
Measure
visual context the fly (almost everything else)

Calculated Yes, as a full new Date tables, disconnected parameter tables, what-if
Once, at refresh time
Table table tables

Interview tip: "Why prefer a measure over a calculated column for a total?" — a calculated column is a fixed value
baked in at refresh, ignoring visual-level filters; a measure recalculates live for whatever rows/filters are currently in
context, which is what a "Total Sales" style calculation almost always needs.

4.2 Basic Measure Examples

Total Sales = SUM(FactSales[Amount])

Order Count = COUNTROWS(FactSales)

Average Order Value = DIVIDE([Total Sales], [Order Count], 0) -- DIVIDE handles /0 gracefully

Distinct Customers = DISTINCTCOUNT(FactSales[CustomerKey])

Always use DIVIDE() instead of the / operator in production DAX — it accepts a third argument as the
fallback result for division by zero, avoiding an error/blank that can silently break a visual.
 5. DAX Context: Row Context vs Filter Context ADVANCED

This is the single most-tested DAX concept in interviews. Nearly every "why doesn't my DAX return what I expect"
question traces back to a misunderstanding here.

5.1 Row Context

Exists inside a calculated column (each row is evaluated one at a time, "aware" of its own row's values) and inside
iterator functions like SUMX . Outside of these, there is no automatic row context.

-- Calculated column: row context lets you reference [Quantity] and [UnitPrice]
-- from the SAME row automatically
LineTotal = FactSales[Quantity] * FactSales[UnitPrice]

5.2 Filter Context

The set of filters currently applied to a calculation — from slicers, visual row/column headers, page/report-level
filters, and any CALCULATE modifiers. Every measure is evaluated within whatever filter context the visual
currently provides.

Total Sales = SUM(FactSales[Amount])

-- On a table visual broken out by Region, "Total Sales" for the "West" row
-- is automatically evaluated with filter context Region = "West" - you never
-- wrote that filter explicitly; the visual supplied it.

5.3 Context Transition — Where Most Confusion Happens

When a row context needs to interact with a measure that expects filter context (e.g. calling a measure inside an
iterator, or using CALCULATE inside a row context), DAX performs context transition: it converts the current row
context into an equivalent filter context, effectively wrapping the row in a CALCULATE .

-- Inside a calculated column, calling a measure implicitly triggers context transition:

CustomerLifetimeRank =
RANKX(
ALL(DimCustomer),
CALCULATE([Total Sales]) -- CALCULATE forces the current row into filter context
)

Interview tip: Be ready to explain in your own words: "Row context walks row by row and knows nothing about
filters by default; CALCULATE converts a row context into a filter context (context transition), which is why wrapping
a column reference in CALCULATE inside an iterator behaves differently than referencing it directly."

5.4 Iterators (X-Functions)

-- SUMX evaluates an expression row by row (row context), THEN sums the results
Total Line Revenue =
SUMX(
FactSales,
FactSales[Quantity] * FactSales[UnitPrice]
)

-- vs SUM, which just aggregates an existing column - no expression evaluated per row
Total Sales = SUM(FactSales[Amount])

Use SUMX / AVERAGEX / MAXX etc. whenever the value to aggregate must be computed per row first (e.g. quantity ×
price where no pre-computed "line total" column exists) rather than pulled directly from an existing column.
 6. CALCULATE, Time Intelligence & Iterator Functions ADVANCED

6.1 CALCULATE — The Most Important Function in DAX

CALCULATE evaluates an expression in a modified filter context. It's the mechanism behind nearly all non-trivial
DAX — comparisons, time intelligence, "ignore this filter," and more.

Sales - West Region =

CALCULATE(
[Total Sales],
DimCustomer[Region] = "West"
)

-- REMOVE existing filters on a column/table

Sales - All Regions =
CALCULATE(
[Total Sales],
ALL(DimCustomer[Region])
)

-- KEEP only the current filter's context but ADD another condition
High Value Sales =
CALCULATE(
[Total Sales],
FactSales[Amount] > 500
)

6.2 ALL, ALLEXCEPT, ALLSELECTED — Frequently Confused Trio

Function Removes Common Use

ALL(table/column) All filters on that table/column entirely "% of total" calculations, grand totals

ALLEXCEPT(table, col1, All filters on the table except the listed Keep one dimension's filter, ignore all others
col2) columns on that table

Filters from inside the visual, but keeps "% of visible total" that respects slicers but
ALLSELECTED(table/column)
filters from outside it (slicers/page filters) not the visual's own row/column context

-- % of Total that respects slicers on the report page, but ignores the
-- current row's own category breakdown
Pct of Selected Total =
DIVIDE(
[Total Sales],
CALCULATE([Total Sales], ALLSELECTED(DimProduct))
)

6.3 Time Intelligence Functions

Sales YTD = TOTALYTD([Total Sales], DimDate[Date])

Sales PY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(DimDate[Date]))

Sales YoY % =
VAR CurrentSales = [Total Sales]
VAR PriorSales = [Sales PY]
RETURN DIVIDE(CurrentSales - PriorSales, PriorSales, 0)

Sales MTD = TOTALMTD([Total Sales], DimDate[Date])

Rolling 3-Month Sales =

CALCULATE(
[Total Sales],
DATESINPERIOD(DimDate[Date], MAX(DimDate[Date]), -3, MONTH)
)

All time intelligence functions require a proper, contiguous, marked Date table (Section 2.4) — without it, these
functions either error or silently produce wrong results.

6.4 VAR — Readability and Performance

Profit Margin % =
VAR TotalRevenue = [Total Sales]
VAR TotalCost = SUMX(FactSales, FactSales[Quantity] * FactSales[UnitCost])
VAR Profit = TotalRevenue - TotalCost
RETURN
DIVIDE(Profit, TotalRevenue, 0)

Interview tip: VAR makes complex DAX readable and also improves performance — each variable is evaluated only
once and reused, whereas repeating the same expression inline can force the engine to recompute it multiple times.
7. Advanced DAX Patterns (Interview Favorites) ADVANCED

7.1 Ranking Within a Filter Context

Customer Rank by Sales =

RANKX(
ALL(DimCustomer[CustomerName]),
[Total Sales],
,
DESC
)

7.2 Top N Customers, Dynamically

Top 5 Customer Sales =

VAR TopCustomers =
TOPN(5, ALL(DimCustomer), [Total Sales], DESC)
RETURN
CALCULATE([Total Sales], TopCustomers)

7.3 New vs Returning Customers

First Purchase Date =

CALCULATE(
MIN(FactSales[OrderDate]),
ALLEXCEPT(FactSales, FactSales[CustomerKey])
)

Is New Customer This Period =

IF(
[First Purchase Date] >= MIN(DimDate[Date]) && [First Purchase Date] <= MAX(DimDate[Date]),
"New",
"Returning"
)

7.4 Running Total

Running Total Sales =

CALCULATE(
[Total Sales],
FILTER(
ALLSELECTED(DimDate[Date]),
DimDate[Date] <= MAX(DimDate[Date])
)
)

7.5 Same-Store / Comparable Cohort Analysis

-- Compare only customers present in BOTH the current and prior period
Cohort Sales Comparison =
VAR CurrentCustomers = VALUES(FactSales[CustomerKey])
VAR PriorCustomers =
CALCULATETABLE(
VALUES(FactSales[CustomerKey]),
SAMEPERIODLASTYEAR(DimDate[Date])
)
VAR CommonCustomers = INTERSECT(CurrentCustomers, PriorCustomers)
RETURN
CALCULATE([Total Sales], CommonCustomers)

7.6 Dynamic Measure Switching with a Disconnected Table

MeasureSelector = { "Sales", "Profit", "Quantity" } -- calculated table, no relationship to model

Selected Measure =
VAR Choice = SELECTEDVALUE(MeasureSelector[Value])
RETURN
SWITCH(
Choice,
"Sales", [Total Sales],
"Profit", [Total Profit],
"Quantity", [Total Quantity],
BLANK()
)

Interview tip: This "field parameter" style pattern (letting a user pick which metric a chart shows via a slicer) is a
favorite advanced question because it tests SELECTEDVALUE , SWITCH , and disconnected tables together.

7.7 Handling Many-to-Many with a Bridge Table

-- FactSales <- Bridge_AccountCustomer -> DimAccount

-- A customer can belong to multiple accounts, and an account can have multiple customers
Account Sales (Many-to-Many) =
CALCULATE(
[Total Sales],
Bridge_AccountCustomer
)
 8. Performance Optimization: VertiPaq, Query Folding,
Aggregations ADVANCED

8.1 VertiPaq Compression Basics

Columns with fewer distinct values compress far better (dictionary + run-length encoding) than high-cardinality
columns. A frequently asked optimization: split a high-cardinality timestamp column into a Date key (low
cardinality, joins to DimDate) and a separate Time-of-day column, rather than storing full datetime values directly
in the fact table.

8.2 Reduce Cardinality, Reduce Column Count

Remove unused columns from the model entirely — every column costs memory whether or not it's ever
visualized.
Avoid importing calculated, derived text columns you could compute in DAX instead — unless they're needed
for relationships/filtering (Section 4.1's rule).
Prefer integer surrogate keys over text keys for relationships — integers compress and join faster.

8.3 Performance Analyzer

Power BI Desktop's built-in Performance Analyzer records, per visual, how long is spent in the DAX query engine
vs. rendering — the primary tool for diagnosing which specific visual/measure is slow, and whether the bottleneck
is the query or the visual itself.

8.4 Avoiding Common DAX Performance Traps

-- SLOW: iterates the entire fact table for every row of a large dimension
SlowMeasure =
SUMX(
FILTER(FactSales, FactSales[CustomerKey] = SELECTEDVALUE(DimCustomer[CustomerKey])),
FactSales[Amount]
)

-- FASTER: let the engine's relationship-aware filter context do the work

FastMeasure = CALCULATE(SUM(FactSales[Amount]))
-- (relying on the existing DimCustomer -> FactSales relationship instead of
-- re-implementing the filter manually inside FILTER())

Interview tip: A common senior-level question is "when should you use FILTER() vs just relying on
relationships/CALCULATE?" — the expected answer is that manual FILTER() over a large fact table is usually far
slower than letting the model's existing relationships and CALCULATE's implicit filtering do the same job.

8.5 Aggregations (for Very Large DirectQuery/Import Models)

Define a smaller, pre-aggregated import table (e.g. daily sales by region) that Power BI automatically substitutes
for queries it can answer, falling back to the full-detail table only when a query needs finer granularity —
conceptually similar to materialized views/CTAS in a warehouse, transparent to the report author.

8.6 DAX Studio & Query Plans

DAX Studio (external, free tool) shows the server timings and query plan for a DAX query, distinguishing Storage
Engine time (fast, VertiPaq scans) from Formula Engine time (slower, row-by-row DAX logic) — a high Formula
Engine percentage is the classic signal of an inefficient measure needing rewriting.
 9. Row-Level & Object-Level Security ADVANCED

9.1 Row-Level Security (RLS)

-- DAX filter expression on a Role, e.g. "Regional Manager" role on DimCustomer:

[Region] = USERPRINCIPALNAME()

-- More commonly, via a mapping table so the filter isn't hardcoded per user:
[Region] IN
CALCULATETABLE(
VALUES(SecurityMapping[Region]),
SecurityMapping[UserEmail] = USERPRINCIPALNAME()
)

RLS filters propagate through relationships just like any other filter context — applying RLS on DimCustomer
automatically restricts FactSales for users assigned to that role, as long as the relationship direction allows the
filter to flow (Section 2.3).

9.2 Static vs Dynamic RLS

Type How Trade-off

Hardcoded value per role, e.g. [Region] =

Static Simple, but needs a new role per value — doesn't scale
"West"

Uses USERPRINCIPALNAME() against a mapping Scales to any number of users with one role, standard for
Dynamic
table production

9.3 Object-Level Security (OLS)

Restricts visibility of entire tables or columns (not rows) per role — configured via external tools (Tabular Editor)
rather than the Power BI Desktop UI directly. The go-to answer for hiding a sensitive column (e.g. salary) from
most users entirely, versus RLS which only ever filters which rows are visible.
 10. Power BI Service: Datasets, Dataflows, Gateways, Deployment
INTERMEDIATE

10.1 Dataflows
Power Query logic hosted centrally in the Power BI Service (not tied to one .pbix file), writing standardized entities
to Azure Data Lake Storage — letting multiple reports/datasets reuse the same cleaned, transformed source
instead of duplicating the same Power Query logic in every file.

10.2 On-Premises Data Gateway

A bridge process that lets the cloud-hosted Power BI Service securely reach on-premises data sources (SQL
Server, files) for both scheduled refresh and DirectQuery — required whenever a source isn't itself cloud-
reachable.

10.3 Deployment Pipelines

Dev → Test → Production stages within the Power BI Service, allowing controlled promotion of reports/datasets
with rule-based parameter/connection swapping per stage (e.g. pointing to a dev database in Dev, prod database
in Production) — the Power BI answer to CI/CD environment promotion.

10.4 Workspaces & Datasets

A workspace is a container for related reports/dashboards/datasets, usually mapped to a team or subject area. A
published dataset can be reused across multiple reports ("Power BI as a semantic layer") without re-importing or
re-modeling the data each time — a key point when asked about avoiding duplicated data models across a large
organization.
 11. Incremental Refresh & Large Datasets ADVANCED

11.1 The Problem It Solves

Refreshing an entire multi-year fact table on every scheduled refresh is slow and unnecessary when only recent
data actually changes — incremental refresh partitions the table by date range and only reprocesses the
partitions defined as "refreshing," leaving historical partitions untouched.

11.2 Setting It Up

// Power Query - define RangeStart/RangeEnd parameters (required, exact names)

RangeStart = #datetime(2024, 1, 1, 0, 0, 0)
RangeEnd = #datetime(2024, 1, 2, 0, 0, 0)

= [Link](Source, each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd)

Then in the table's Incremental Refresh policy: e.g. "store 5 years of history, refresh only the last 10 days" —
historical partitions outside the refresh window are never touched again, dramatically cutting refresh time for
large fact tables.

11.3 Detecting Data Changes (Optional Add-On)

An optional "detect data changes" column (e.g. a source LastModified timestamp) lets Power BI skip refreshing a
partition entirely if nothing actually changed in that range — a further optimization on top of the basic date-range
partitioning.
 12. Composite Models: Import vs DirectQuery vs Dual ADVANCED

12.1 Storage Mode Comparison

Mode Data Location Refresh Speed Data Freshness

Loaded into VertiPaq (in-

Import Very fast queries As fresh as the last scheduled refresh
memory)

Stays in the source; query

DirectQuery Slower, source-dependent Real-time, always current
translated live

Both — cached AND Chooses whichever is faster Flexible, used for shared dimension tables in
Dual
queryable live per query composite models

12.2 Composite Models

Mixing Import and DirectQuery tables in the same model — e.g. a large, rapidly changing fact table in
DirectQuery, joined to a small, mostly-static dimension table set to Dual so it doesn't force every query back to
the source unnecessarily. This is the standard architecture question: "how do you get near-real-time fact data
without sacrificing dimension query speed?"

12.3 DirectQuery Trade-offs

Every visual interaction sends a live query to the source — performance is only as good as the underlying
database and its indexing/tuning.
Many DAX functions are restricted or behave differently in DirectQuery (e.g. certain complex time intelligence
can be slow or unsupported without specific engine settings).
No VertiPaq compression benefit — you inherit whatever performance characteristics the source database has.

Interview tip: "When would you choose DirectQuery over Import?" — when data must be real-time/near-real-time
(e.g. operational dashboards), when the source is too large to fit in available memory even with compression, or
when data governance requires data to never leave the source system.
 13. Integration with the Data Engineering Stack INTERMEDIATE

13.1 Common Source Connectors

Source Typical Pattern

Azure Synapse / Data

Import or DirectQuery over curated Parquet/Delta tables
Lake

Databricks Native Databricks connector (via SQL Warehouse) — often on top of Delta Lake gold tables

Snowflake / Redshift DirectQuery or Import via native connector, usually pointed at a pre-aggregated BI-layer schema

Azure Data Factory Upstream orchestrator that lands/curates data before Power BI ever touches it — not a Power BI
(ADF) data source itself

13.2 Where the "BI Layer" Should Live

A recurring interview theme: heavy transformation logic belongs upstream in the data engineering pipeline
(Glue/dbt/Spark), not buried in Power Query or DAX. Power BI should consume already-conformed, well-modeled
curated tables (a gold/serving layer) rather than re-implementing joins and business logic that a data engineering
pipeline should have already handled — keeping the semantic model thin, fast, and maintainable.

13.3 Semantic Layer & the "Single Source of Truth" Pitch

A shared, certified Power BI dataset acts as the enterprise semantic layer — the same measure definitions (e.g.
"Total Sales") used consistently across every report, avoiding the classic problem of five teams calculating
"revenue" five slightly different ways.
 14. Common Interview Questions — Fully Answered

Q1. Why does a total at the bottom of a table visual not equal the sum of the rows
above it?
Answer: Almost always a filter context issue — the measure uses CALCULATE with a filter modifier (like ALL or a
hardcoded condition) that behaves differently once the visual's row-level filter context is removed at the grand-
total row, or the measure involves DISTINCTCOUNT /ratios that don't sum linearly (e.g. distinct customers per row
won't sum to the true distinct total across all rows).

Q2. A DirectQuery report is slow. How do you approach diagnosing it?

Answer: Use Performance Analyzer to isolate whether time is spent in the DAX/SQL query or in visual rendering;
check the "View Native Query" / generated SQL for inefficiency; verify the source database has appropriate
indexing for the columns being filtered/joined; consider switching stable dimension tables to Dual mode in a
composite model to avoid unnecessary round-trips.

Q3. How do you implement "compare this year to last year" in DAX?
Answer: Use CALCULATE with SAMEPERIODLASTYEAR (or DATEADD ) against a marked, contiguous Date table, as
shown in Section 6.3, then compute the delta/percentage with DIVIDE to safely handle a zero prior-year value.

Q4. How would you restrict a regional sales manager to only see their region's data?
Answer: Row-Level Security with a dynamic DAX filter driven by USERPRINCIPALNAME() against a mapping table
(Section 9.1), applied on the dimension side so it propagates through the relationship to the fact table — not
hardcoded per-user static roles, which don't scale.

Q5. Refresh time has grown from 10 minutes to 2 hours as the fact table grew. What
do you do?
Answer: Set up incremental refresh so only recent partitions reprocess on each run (Section 11); check for
broken query folding introduced by a recent Power Query change (Section 3.2); consider whether some historical
detail could move to an aggregation table instead of being fully imported at row-level grain.

Q6. What's the difference between a calculated column and a measure, and when
would using the wrong one cause a real bug?
Answer: A calculated column is fixed at refresh time and ignores later filter context; using one for something like
"% of total" would freeze that percentage based on the whole table at refresh time rather than recalculating per
the user's current slicer selection — producing a value that looks plausible but silently doesn't respond to
interaction, which is a hard bug to spot without knowing this distinction.
15. DAX Practice Problems (Solve These Yourself)

Q1. Write a measure that shows each product's sales as a percentage of its category's total sales, correctly
responding to slicers on Region.
Hint: combine ALLEXCEPT or a CALCULATE/ALLSELECTED pattern from Sections 6.2 and 7.

Q2. Write a measure for "Customers Lost" — customers who purchased in the prior period but have zero
purchases in the current period.
Hint: adapt the cohort pattern in Section 7.5, but look for customers in PriorCustomers NOT in
CurrentCustomers (EXCEPT instead of INTERSECT).

Q3. A calculated column computing "Days Since Last Order" per customer is refresh-time-static and doesn't
update between refreshes. A stakeholder wants it to reflect "as of today" dynamically. How do you redesign it?
Hint: convert to a measure using TODAY() and MAX(FactSales[OrderDate]) with appropriate filter context via
CALCULATE/ALLEXCEPT.

Q4. Design a composite model architecture for a company with a 2-billion-row transaction table that must
show near-real-time data alongside a slowly-changing 500-row product dimension.
Hint: DirectQuery fact table + Dual-mode dimension table, Section 12.2.

Q5. A report's "Total Sales" grand total is correct, but a "% of Total" column shows 100% on every single row
instead of varying percentages. Diagnose the likely DAX bug.
Hint: the denominator CALCULATE is probably missing an ALL()/ALLSELECTED() modifier, so it's re-evaluating
with the SAME filter context as the numerator instead of the unfiltered/less-filtered total.
16. Quick-Reference Cheat Sheet

Need to… Use

Compute a value per row using other columns in that row Calculated column (row context)

Aggregate that responds live to slicers/filters Measure (filter context)

Compute a row-by-row expression then aggregate it SUMX / AVERAGEX iterator

Modify the current filter context CALCULATE

Remove all filters on a table/column ALL()

Remove all filters except specific columns ALLEXCEPT()

Respect external slicers but ignore visual's own context ALLSELECTED()

Compare to same period last year SAMEPERIODLASTYEAR / CALCULATE

Avoid divide-by-zero errors DIVIDE(x, y, 0)

Push transformation logic to the source, not Power BI Verify query folding (View Native Query)

Restrict data by user identity Dynamic Row-Level Security via USERPRINCIPALNAME()

Hide a column/table entirely by role Object-Level Security (Tabular Editor)

Cut refresh time on a huge fact table Incremental refresh with RangeStart/RangeEnd partitions

Get near-real-time facts + fast dimension queries Composite model: DirectQuery fact + Dual-mode dimension

Diagnose a slow visual Performance Analyzer (and DAX Studio for query plans)

Share one certified metric definition org-wide A shared, published Power BI dataset (semantic layer)

Final interview advice: Power BI interviews for data-engineering-adjacent roles reward candidates who can clearly
separate three concerns — what happens in Power Query (before load), what's stored in the model (calculated
columns/tables), and what's computed live at query time (measures/DAX filter context). Framing an answer around
which of these three stages a problem belongs to is a reliable way to sound structured and senior, even under
pressure.

Compiled study guide · pair this with hands-on practice building a real star-schema model in Power BI Desktop rather than reading alone.