DAX Interview Questions for Power BI Developers

DAX interview questions look like syntax questions, but nearly all of them test one idea: evaluation context. Which rows does this expression see, and why? Wrong answers usually produce plausible numbers, which is why interviewers love them. The answers below come from real DAX measures evaluated by the tabular engine inside Excel's Data Model, the same kind of engine that runs DAX in Power BI. The data is the December 2010 order lines of a real UK online retailer (UCI: Online Retail): 26,160 lines from 885 customers in 22 countries.

import pandas as pd
import win32com.client

retail = pd.read_excel("Online Retail.xlsx")                     # UCI Online Retail, one row per order line
lines = retail[(retail.InvoiceDate < "2011-01-01") & retail.CustomerID.notna() & (retail.Quantity > 0)]
lines = lines[["InvoiceNo", "CustomerID", "Country", "Quantity", "UnitPrice"]].astype({"InvoiceNo": str, "CustomerID": int})
print(f"December 2010: {len(lines):,} order lines, {lines.CustomerID.nunique():,} customers, {lines.Country.nunique()} countries")

excel = win32com.client.DispatchEx("Excel.Application")         # a separate, hidden Excel instance
excel.Visible, excel.DisplayAlerts = False, False
book = excel.Workbooks.Add()
sheet = book.Worksheets(1)
sheet.Name = "Sales"
end = len(lines) + 1
sheet.Range(f"A1:E{end}").Value = [tuple(lines.columns)] + [tuple(r) for r in lines.itertuples(index=False)]
sheet.ListObjects.Add(1, sheet.Range(f"A1:E{end}"), None, 1).Name = "Sales"
book.Connections.Add2("Sales", "", "WORKSHEET;" + book.FullName, "Sales", 7, True, False)   # load the table into the Data Model
model = book.Model
table = model.ModelTables("Sales")
out = book.Worksheets.Add()


def measure(name, dax):
    model.ModelMeasures.Add(name, table, dax, model.ModelFormatGeneral)


def value(name, *members):                                       # evaluate a measure with the DAX engine via CUBEVALUE
    args = "".join(f',"{m}"' for m in members)
    out.Range("A1").Formula = f'=CUBEVALUE("ThisWorkbookDataModel","[Measures].[{name}]"{args})'
    excel.CalculateUntilAsyncQueriesDone()
    shown = out.Range("A1").Text                                 # what the cell displays, including errors
    return shown if shown.startswith("#") else (out.Range("A1").Value if shown else "(blank)")


measure("Revenue", "SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])")
measure("Wrong revenue", "SUM(Sales[Quantity]) * SUM(Sales[UnitPrice])")
print(f"\nD1 iterator vs aggregator: SUMX(qty * price) {value('Revenue'):,.0f} | SUM(qty) * SUM(price) {value('Wrong revenue'):,.0f}")

measure("Share of all countries", "DIVIDE([Revenue], CALCULATE([Revenue], ALL(Sales[Country])))")
uk = "[Sales].[Country].&[United Kingdom]"
print(f"\nD2 CALCULATE and ALL: United Kingdom revenue {value('Revenue', uk):,.0f}, "
      f"{value('Share of all countries', uk):.1%} of all countries")

measure("Average per customer, SUM", "AVERAGEX(VALUES(Sales[CustomerID]), SUM(Sales[Quantity]))")
measure("Units", "SUM(Sales[Quantity])")
measure("Average per customer, measure", "AVERAGEX(VALUES(Sales[CustomerID]), [Units])")
print(f"\nD3 context transition: AVERAGEX over customers with SUM(...) {value('Average per customer, SUM'):,.0f} units | "
      f"with the [Units] measure {value('Average per customer, measure'):,.0f} units")

measure("Big orders", "CALCULATE(DISTINCTCOUNT(Sales[InvoiceNo]), Sales[Quantity] >= 1000)")
measure("Big orders or zero", "[Big orders] + 0")
fr = "[Sales].[Country].&[France]"
print(f"\nD4 blanks: big orders in France {value('Big orders', fr)} | with + 0: {value('Big orders or zero', fr):.0f}")

measure("Share with slash", "[Big orders] / CALCULATE([Big orders], Sales[Country] = \"France\")")
measure("Share with DIVIDE", "DIVIDE([Big orders], CALCULATE([Big orders], Sales[Country] = \"France\"))")
print(f"D5 dividing by an empty result: '/' gives {value('Share with slash')} | DIVIDE gives {value('Share with DIVIDE')}")
measure("Rank", "RANKX(ALL(Sales[Country]), [Revenue])")
measure("Rank, rounded", "RANKX(ALL(Sales[Country]), ROUND([Revenue], 2), ROUND([Revenue], 2))")
print("\nD6 RANKX over all countries: raw [Revenue] vs revenue rounded to cents")
for c in ("United Kingdom", "Germany", "France"):
    m = f"[Sales].[Country].&[{c}]"
    print(f"   {c:<15} revenue {value('Revenue', m)!r:<20} rank {int(value('Rank', m))} | rounded rank {int(value('Rank, rounded', m))}")
book.Close(False)
excel.Quit()

December 2010: 26,160 order lines, 885 customers, 22 countries

D1 iterator vs aggregator: SUMX(qty * price) 572,714 | SUM(qty) * SUM(price) 25,586,852,291

D2 CALCULATE and ALL: United Kingdom revenue 498,662, 87.1% of all countries

D3 context transition: AVERAGEX over customers with SUM(...) 312,280 units | with the [Units] measure 353 units

D4 blanks: big orders in France (blank) | with + 0: 0
D5 dividing by an empty result: '/' gives #NUM! | DIVIDE gives (blank)

D6 RANKX over all countries: raw [Revenue] vs revenue rounded to cents
   United Kingdom  revenue 498661.85000000015   rank 2 | rounded rank 1
   Germany         revenue 15241.140000000001   rank 3 | rounded rank 2
   France          revenue 9616.310000000001    rank 3 | rounded rank 3

D1. What is the difference between SUM and SUMX?

SUM adds up a column. SUMX evaluates an expression for each row of a table and adds the results (DAX: SUMX). Revenue is quantity times price for each line, so it needs SUMX: 572,714. Multiplying the column totals instead, SUM(Quantity) * SUM(UnitPrice), gave 25,586,852,291, about 45,000 times too much. It is the classic interview trap: the right answer is a per-row calculation, then a sum.

D2. What does CALCULATE do, and why is ALL used inside it?

CALCULATE evaluates an expression with a modified filter context (DAX: CALCULATE). Inside it, ALL removes filters from a column or table (DAX: ALL). A "share of all countries" measure divides revenue in the current filter by revenue with the country filter removed. For the United Kingdom, revenue was 498,662, 87.1% of all countries that month. Without ALL, the denominator would also be filtered to the United Kingdom, and every country would show 100%.

D3. What is context transition?

When a measure, or an explicit CALCULATE, is evaluated inside a row context, such as an iterator, the current row becomes a filter. That is context transition, and it happens automatically for measures (DAX: CALCULATE). Averaging units per customer with AVERAGEX(VALUES(CustomerID), SUM(Quantity)) returned 312,280 units, the total for everyone, because a bare SUM inside the iterator ignores the current customer. Using the [Units] measure instead gave 353 units per customer, the right answer. The two formulas look almost identical; only one filters by the row.

D4. Why do some cells show blank instead of zero?

Because a measure over no rows returns BLANK, and visuals hide blank rows. There were no orders of 1,000 or more units from France in the month, so the measure returned blank. Adding + 0 made it 0. Whether to do that is a design decision: zeros make every country appear in a table, even ones with no activity, which can flood a report. Say which you want and why.

D5. Why use DIVIDE instead of the slash operator?

Because DIVIDE handles division by zero or blank safely, returning BLANK or an alternate value you choose (DAX: DIVIDE). Dividing by France's empty count with / produced an error, shown as #NUM! in the cell; DIVIDE returned blank. In a report, the slash version breaks visuals or displays infinity, while DIVIDE degrades gracefully.

D6. Why did RANKX put the biggest country second?

A real trap that appeared while building this example. Ranking countries with RANKX(ALL(Country), [Revenue]) put the United Kingdom, with 87% of revenue, in second place, and Germany and France tied in third. The cause is floating-point arithmetic: the engine adds the same order lines in a different order when it evaluates each country inside RANKX, so the United Kingdom's revenue came out as 498,661.85000000015 in one place and a fraction more in the other, and the country was outranked by itself. Rounding both the ranked expression and the value, RANKX(ALL(Country), ROUND([Revenue], 2), ROUND([Revenue], 2)), gave the correct ranks 1, 2 and 3. Round currency before ranking or comparing it.

Other DAX questions to expect

• Calculated column or measure? A calculated column is computed per row when data refreshes and stored in the model; a measure is computed at query time in the current filter context. Use columns for values you filter or slice by, measures for aggregations (DAX overview).
• Why does time intelligence need a date table? Functions such as TOTALYTD and SAMEPERIODLASTYEAR expect a continuous, marked date table related to the facts.
• FILTER or a simple CALCULATE filter? Simple column predicates in CALCULATE are faster and clearer; FILTER over a whole table is for conditions that need several columns or measures.

How to answer DAX questions

• Name the context first: which filters are active, and is there a row context?
• Use iterators for per-row maths, and measures inside iterators when you need context transition.
• Handle blanks and division explicitly.

The language is taught in the DAX guide: measures, calculated columns and context and time intelligence in YTD, MTD and comparisons in DAX. Modelling, which decides how filters flow, is covered in data modelling in Power BI. Spreadsheet questions are in Excel interview questions.

DAX and data modelling are part of the Power BI course.

Sources

• SUMX function (DAX reference, Microsoft Learn) — Microsoft
• CALCULATE function (DAX reference, Microsoft Learn) — Microsoft
• ALL function (DAX reference, Microsoft Learn) — Microsoft
• DIVIDE function (DAX reference, Microsoft Learn) — Microsoft
• DAX overview (Microsoft Learn) — Microsoft
• Online Retail dataset (UCI Machine Learning Repository) — UCI Machine Learning Repository