Interviewers rarely want definitions recited back. They want to hear that you’ve used the thing and know where it goes wrong. For each question below there’s a short answer, and the point that shows experience.
Basics
1. What’s the difference between Power Query and DAX? Power Query (M) shapes data before it’s loaded: cleaning, merging, unpivoting. DAX calculates after loading, on the model. Shows experience: “I shape data as far upstream as I can, so DAX stays simple.”
2. Measure or calculated column? A calculated column is computed once per row at refresh and stored. A measure is computed when a visual asks for it, under the current filters. Shows experience: “Totals and ratios are always measures. A column is for something I need to slice or filter by.”
3. What is a star schema? Fact tables (events, like sales) in the middle, dimension tables (who, what, when, where) around them, connected one-to-many. Shows experience: “One flat table works until you need a second fact table or time intelligence.”
4. What does a relationship’s cross-filter direction do? It decides which way filters flow. Single (dimension filters fact) is the default and the safe choice. Shows experience: “I use Both only for a specific reason, because it can make filters ambiguous and slow.”
DAX
5. What does CALCULATE do?
It evaluates an expression after changing the filter context. A filter argument on a column replaces the existing filter on that column. Shows experience: mention KEEPFILTERS to combine instead of replace.
6. Filter context vs row context?
Filter context is the set of filters on the model (from slicers, visuals and CALCULATE). Row context is “the current row” inside a calculated column or an iterator like SUMX. Row context does not filter anything by itself.
7. What is context transition?
When CALCULATE (or a measure reference, which has a hidden CALCULATE) runs inside a row context, the current row becomes a filter. That’s why SUMX(Customers, [Total Sales]) works per customer.
8. ALL vs REMOVEFILTERS vs ALLEXCEPT vs ALLSELECTED?
REMOVEFILTERS clears filters (and only that). ALL does the same as a CALCULATE modifier but can also return a table. ALLEXCEPT clears all but the listed columns. ALLSELECTED keeps what the user chose in slicers but removes the visual’s own grouping, which is useful for “% of visible total”.
9. Why use DIVIDE instead of the / operator?
DIVIDE returns blank (or an alternative you choose) when dividing by zero, instead of an error.
10. Why does my total not equal the sum of the rows?
Totals re-run the measure without the row filter; they don’t add the rows. Fix it with SUMX over the grouping column, or hide it with ISINSCOPE. Full explanation.
11. How do you write year-to-date and last year?
TOTALYTD([Total Sales], Dates[Date]) and CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dates[Date])). Shows experience: “Both need a proper date table: every day, no gaps, marked as date table.”
12. When do you use variables? To avoid computing the same thing twice, to make long measures readable, and to debug by returning a variable instead of the result. Variables are evaluated once, where they’re defined.
Modeling and performance
13. You have Order Date and Ship Date. How do you analyse by both?
One active relationship to the date table, one inactive. A measure turns on the inactive one with USERELATIONSHIP.
14. A report is slow. Where do you start?
Performance analyzer in Power BI Desktop to find the slow visual, then the DAX query behind it (DAX Studio). Common causes: iterating a big fact table with FILTER where a simple column filter would do, bi-directional relationships, high-cardinality columns, and too many visuals on one page.
15. Import, DirectQuery or Direct Lake? Import is the fastest to query and the most flexible. DirectQuery leaves data at the source, for very large or near-real-time data, at the cost of speed and some DAX limits. Direct Lake (Fabric) reads Delta tables in OneLake without a scheduled import.
Sharing and security
16. How does row-level security work?
You define roles with DAX filters on tables, for example Countries[Manager] = USERPRINCIPALNAME(). Filters then flow through relationships. Assign people to roles in the Power BI service, and test with View as.
17. Workspace, app, or Publish to web? Workspaces are for building with colleagues; apps are for distributing to readers; Publish to web is public to anyone and must never contain company data.
Live coding
Senior interviews often add a live task, such as “return each country’s share of the total” or “return the name of the top country”. Practise writing these without autocomplete:
Country Share = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(financials[Country])))The Senior interview project has these tasks with instant checks, and the earlier job projects cover the take-home tests companies send before an interview.
Sources
- DAX function reference, Microsoft Learn
- Star schema and the importance for Power BI, Microsoft Learn
- Row-level security with Power BI, Microsoft Learn
- Use Performance Analyzer, Microsoft Learn