Power BI and DAX interview questions with model answers

The model assumed is hypothetical: a Sales fact table (OrderDate, ProductKey, StoreKey, Qty, Amount, Cost), dimension tables Product, Store and a Date table.

Modelling questions

1. What is a star schema and why does Power BI prefer it?

One fact table of events (sales lines) surrounded by dimension tables (product, store, date), each related one-to-many to the fact. Filters flow from dimensions to the fact, measures stay simple, and the VertiPaq engine compresses repeated dimension keys well. A single flat table works for a demo but duplicates attributes on every row and makes time intelligence awkward. Checking: do you think about the model before visuals.

2. When would you set a relationship's cross-filter direction to Both?

Rarely, and deliberately — for example to let a slicer on a bridge table filter through a many-to-many relationship. Bidirectional filters can create ambiguous paths and slow queries. The default single direction, dimension to fact, is the answer the interviewer hopes to hear first. Checking: restraint.

3. Calculated column or measure for profit?

Row-level profit (Amount - Cost) could be a column, but it costs memory on every row. A measure is usually better:

Total Profit = SUMX ( Sales, Sales[Amount] - Sales[Cost] )

A column is justified when you need to slice or filter by the value, such as a "profit band" used in a slicer.

Filter context questions

4. What is the difference between SUM and SUMX?

SUM(Sales[Amount]) aggregates one column. SUMX(Sales, expression) iterates the table row by row, evaluates the expression in row context and adds the results. You need SUMX when the value does not exist as a column, such as Qty * Price. Summing a stored column with SUMX gives the same answer as SUM.

5. Explain filter context using one visual.

In a matrix with Store on rows, each cell is evaluated with a filter on that store; the total row has no store filter. The same measure therefore returns a different value per cell with no change in the formula. Context comes from rows, columns, slicers, page and report filters.

6. Write a measure for each product's share of all products, which still respects the Store slicer.

Share of Products =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], REMOVEFILTERS ( Product ) )
)

REMOVEFILTERS(Product) clears only filters coming from the Product table, so a store or date selection still applies to the denominator. Using ALL(Sales) would clear the store filter too and give share of the grand total — a different business question. DIVIDE returns blank instead of an error when the denominator is zero. Checking: the most discriminating question in the round; weak candidates say "use ALL" without saying all of what.

7. What does CALCULATE do?

It evaluates an expression in a modified filter context. Its filter arguments add to or replace existing filters on the same columns. It also performs context transition: when called inside a row context, such as inside SUMX or a calculated column, it turns the current row into an equivalent filter. That is why a measure referenced inside an iterator behaves as if CALCULATE wraps it.

Time intelligence questions

8. Year-to-date sales for an Indian financial year ending 31 March.

Sales FYTD = TOTALYTD ( [Total Sales], 'Date'[Date], "3/31" )

The third argument sets the year-end date. Without it, TOTALYTD resets on 1 January, which is wrong for an April–March business. The Date table should also carry a financial-year column so the matrix groups by FY 2026-27 rather than by calendar year.

9. Growth versus same period last year.

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

Sales YoY % = DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] )

Mention the prerequisites: a contiguous Date table related to Sales[OrderDate] and marked as the date table. If the interviewer asks about an incomplete current month, say you would compare only up to the last date with data, otherwise a partial September looks like a collapse against a full one.

Security and performance questions

10. How do you let each regional manager see only their region?

Row-level security. In Modeling, Manage roles, add a role with a DAX filter on the Store table, such as a static rule [Region] = "North", or a dynamic rule using the signed-in user:

[ManagerEmail] = USERPRINCIPALNAME ()

The dynamic version needs a mapping table of managers to regions. Test with "View as" before publishing, and assign users to the role in the Power BI service. RLS restricts data; it is not a substitute for hiding a page.

11. A report is slow. What do you check?

In order: Performance Analyzer to find the slow visual; whether that visual's measure iterates a large table with a complex expression; high-cardinality columns such as full timestamps or transaction IDs that could be removed or split into date and time; bidirectional relationships; and whether Power Query steps still fold back to the source. Import mode is usually faster than DirectQuery; DirectQuery is chosen when data must be near real time or is too large to import, and it has modelling limits.

Answering the "explain your dashboard" follow-up

After the technical questions, many interviewers open your portfolio report and ask why a number is what it is. Pick one card, say which measure drives it, what filters apply on that page, and one check you ran to confirm the figure against the source. That short explanation shows the same skill as question 6, on your own work.

Power BI modelling and DAX are built up over several weeks of the Data Analyst programme. If your reporting is still mainly in spreadsheets, the advanced Excel question bank covers the pivot concepts these questions assume.