Skip to content
Tutorials Center
My learning

Topics

Lesson 55 of 56 ยท Practice

Power BI Interview Questions

25 Power BI interview questions with clear answers on data modelling, DAX, Power Query and the Power BI Service, plus runnable stand-ins for the trickiest ideas.

Power BI interviews tend to follow the same arc. First comes the model ("how would you structure this data?"), then Power Query ("how do you get it clean?"), then DAX ("why does this measure give that number?") and finally the service ("how do people see it, and who is allowed to?"). Interviewers care less about memorised definitions than about whether you can explain a trade-off and spot the cause of a wrong number.

This lesson gives 25 questions in those four groups, each with a short answer you could say out loud. Power BI itself cannot run on this site, so the DAX and M snippets are read-only, and two pandas stand-ins demonstrate the ideas candidates most often get wrong: calculated column versus measure, and context transition. Answer each question yourself before reading ours. Where a topic has its own lesson, you will find a link.

How an interview usually flows

Model Star schema, relationships, storage modes.
Power Query Cleaning, folding, refresh.
DAX Context, CALCULATE, time.
Service Refresh, security, sharing.

Explain the trade-off, not only the definition.

1 / 2

Data modelling

1. What is a star schema and why does Power BI prefer it? A star schema has central fact tables (events, such as sales lines) surrounded by dimension tables (descriptions, such as product, customer and date). Dimensions filter facts through one-to-many relationships. Power BI's engine compresses and scans narrow, well-keyed tables quickly, and filters flow naturally from the dimensions to the facts, so DAX is simpler and reports are faster than on one wide flat table. See Star Schema.

2. What is the grain of a fact table and why does it matter? The grain is what one row represents, for example "one order line". Every measure, relationship and join depends on it. Mixing grains in one table (an order total next to line items) leads to double counting. Decide the grain first, and state it in the table's description.

3. Which relationship cardinalities exist, and which do you prefer? One-to-many (the normal case), one-to-one and many-to-many. Prefer one-to-many from a dimension with a unique key to a fact table. A many-to-many relationship is a last resort, for example between two fact tables at different grains, and it is usually better to introduce a bridge or a shared dimension. Duplicate keys on the "one" side stop the relationship from being created as one-to-many.

4. What is cross-filter direction? When would you use both? It sets whether filters flow only from the one side to the many side (single) or in both directions. Single is the default and the safest. Bidirectional filtering can resolve a specific need, such as a slicer filtered by another fact table, but it can create ambiguity and slow queries. Often a measure with CROSSFILTER or a better model solves the same problem. See Relationships, Cardinality and Filter Direction.

5. How do you handle an orders table with an order date and a ship date? Use a role-playing dimension: one date table, one active relationship (say, order date) and one or more inactive relationships (ship date) that a measure activates with USERELATIONSHIP. This avoids duplicating the date table.

6. Explain Import, DirectQuery, Dual and composite models. Import copies data into the model: fastest, but data is as fresh as the last refresh and size is limited. DirectQuery sends queries to the source at view time: always current, but speed depends on the source and some features are limited. Dual tables can act as either, so a small dimension joins quickly with both. A composite model mixes the storage modes. Choose Import unless freshness or data volume forces something else. See Import, DirectQuery and Live Connection.

7. Why do you need a date table, and what does "Mark as date table" do? Time intelligence functions such as TOTALYTD and SAMEPERIODLASTYEAR need a continuous column of dates. A date table has one row per day, without gaps, covering all the data. Marking it tells the engine which column is the key, so time intelligence behaves correctly even when the fact table has missing days. See Date Tables and Time Intelligence Setup.

Power Query

8. What is query folding and how do you check it? Folding means Power Query translates steps into a single native query (such as SQL) that the source executes, instead of loading everything and transforming it in the mashup engine. Right-click a step and check whether View Native Query is available; if it is greyed out, the step did not fold. Steps that fold early (filters, column selection) reduce the data moved. See Query Folding, Incremental Refresh and Query Performance.

9. What is the difference between Merge and Append? Append stacks rows of tables with similar columns (like UNION ALL). Merge joins tables side by side on key columns, with a join kind (left outer, inner, anti and so on). See Merge and Append Queries.

10. Reference or duplicate: what is the difference? A duplicate copies the query steps, so the new query repeats the work against the source. A reference starts from the output of another query, so the source is read as part of the parent and the child continues from there. Use reference for staging queries that feed several outputs.

11. How does incremental refresh work? You define two date/time parameters named RangeStart and RangeEnd, filter the fact table on them, and set a policy (for example, store five years, refresh the last ten days). The service then loads data in partitions and refreshes only the recent ones. It works best when the filter folds to the source.

12. When would you unpivot? When the source has a column per period or category (Jan, Feb, Mar), which makes measures awkward. Unpivoting gives one row per product and month with a single value column. Use "unpivot other columns" so new columns are included at refresh. See Unpivot, Pivot and Transpose.

13. Should a calculation be done in Power Query, a calculated column or a measure? As far upstream as possible. Row-level cleaning and static attributes belong in the source or Power Query. A calculated column is stored in the model and computed at refresh, which suits attributes used in slicers. A measure is computed at query time and reacts to filters, so anything that depends on what the user selects (totals, ratios, growth) must be a measure.

DAX

14. What is the difference between a calculated column, a measure and a calculated table? A calculated column is evaluated per row at refresh and stored (uses memory). A measure is evaluated at query time under the current filter context and stores nothing. A calculated table is a table produced by a DAX expression at refresh. See DAX Basics.

15. Explain row context and filter context. Filter context is the set of filters applied to the model (slicers, visual axes, rows and columns of a matrix, CALCULATE arguments); aggregators like SUM respect it. Row context means "the current row" inside a calculated column or an iterator such as SUMX; it lets an expression refer to column values of that row, but it does not filter other tables on its own.

16. 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: CALCULATE ( [Sales], Product[Colour] = "Red" ) replaces any existing filter on Product[Colour]. Modifiers such as REMOVEFILTERS and KEEPFILTERS change this behaviour. See CALCULATE and Filter Context.

17. What is context transition? When CALCULATE (or a measure reference) is evaluated inside a row context, the values of the current row's columns become filters. That is why a measure used in a calculated column or inside SUMX returns the row's own total rather than the grand total. The second stand-in below shows it.

18. SUM versus SUMX? SUM adds one column. SUMX iterates a table, evaluates an expression for each row and adds the results, which is needed when the value is a calculation across columns, such as Quantity * Price. See Aggregators and Iterators.

19. ALL, ALLSELECTED and REMOVEFILTERS? ALL and REMOVEFILTERS remove filters from a table or columns (the latter can only be used as a CALCULATE modifier). ALLSELECTED removes the filters from the visual but keeps the ones from outside, such as slicers, so percent of visible total works. See Ranking, Top N and Percent of Total.

20. Why use variables? Variables (VAR) are calculated once and can be reused, which improves speed and readability. They also fix a value in the context where they are defined, which avoids surprises when used later inside CALCULATE.

21. How do you compute year to date and growth against last year? With a date table, TOTALYTD ( [Sales], 'Date'[Date] ) for year to date, CALCULATE ( [Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) for last year and DIVIDE for the growth. See Time Intelligence.

22. DIVIDE or the / operator? DIVIDE handles a zero or blank denominator by returning blank (or an alternative you give), while / returns an error or infinity. Use DIVIDE for ratios.

23. A report is slow. How do you investigate? Measure first: Performance Analyzer in Desktop shows the time for each visual and lets you copy its query; DAX Studio shows the query plan. Typical fixes: a star schema in place of flat tables, remove unused columns and high-cardinality columns, replace bidirectional filters, simplify measures with variables, reduce visuals per page and use aggregations or Import mode where suitable. See Debugging and Optimizing DAX.

The Power BI Service

24. How do you control who sees what? At three levels. Workspace roles (Admin, Member, Contributor, Viewer) define who can edit content. Apps or direct sharing give audiences read access. Row-level security restricts rows by role, either with a static filter or a dynamic one using USERPRINCIPALNAME. RLS applies to viewers; people with edit rights in the workspace can see all the data of the model. See Row-Level Security.

25. How does scheduled refresh work for on-premises data? An on-premises data gateway installed on a machine that can reach the source brokers the connection. You add the data source to the gateway, enter credentials and schedule the refresh. Pro licences allow up to eight scheduled refreshes per day, while Premium capacity allows more. A personal gateway suits one user; a standard gateway is shared and managed for an organisation. See Scheduled Refresh and Gateways.

Scenario and DAX questions

Interviewers also like short scenario questions: "Here is a situation, how would you do it?" The answers below give the shape of a good reply: the idea first, then the DAX or the steps, then a caution. The DAX is read-only, and the stand-in after the list computes the numeric answers in pandas.

26. How do you find the second-highest value (for example the second-best product)? Build a virtual table of products with their sales, take the top two with TOPN, and return the smaller of the two:

DAX
Second Highest Sales =
VAR ProductSales =
    ADDCOLUMNS ( VALUES ( Product[Name] ), "Sales Value", [Total Sales] )
RETURN
    MINX ( TOPN ( 2, ProductSales, [Sales Value], DESC ), [Sales Value] )

TOPN can return more than two rows when values tie at the cutoff, so say whether "second highest" means the second row (ties counted separately) or the second distinct value. INDEX ( 2, ..., ORDERBY ( [Total Sales], DESC ) ) is an alternative, and RANKX can label every product with its rank. See Ranking, Top N and Percent of Total.

27. How do you calculate month-over-month and year-over-year growth? With a date table, shift the filter and divide the difference by the previous value:

DAX
Sales PM = CALCULATE ( [Total Sales], DATEADD ( 'Date'[Date], -1, MONTH ) )
MoM % = DIVIDE ( [Total Sales] - [Sales PM], [Sales PM] )

Sales PY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
YoY % = DIVIDE ( [Total Sales] - [Sales PY], [Sales PY] )

DIVIDE returns blank for the first period instead of an error. The date table must be complete and marked (Time Intelligence).

28. How do you show each product's percent of total? Divide by the total with the product filter removed, keeping slicers that the user chose:

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

Use ALL instead of ALLSELECTED when the denominator must ignore slicers too. The visual's own Show value as option offers a quick version.

29. How do you calculate cumulative (running) sales? Sum all dates up to the current one:

DAX
Cumulative Sales =
CALCULATE (
    [Total Sales],
    FILTER ( ALLSELECTED ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)

For a running total that restarts every year use TOTALYTD, and for a position-based version use the WINDOW function (see the window functions section of Time Intelligence).

30. Sales are in two tables, online and store. How do you report them together? First check that they have the same grain and columns. If so, append them in Power Query and add a Channel column, so one fact table feeds one set of measures and every slicer works (Merging and Appending Queries). If the tables differ (online has sessions, the store has registers), keep two fact tables, connect both to shared dimensions (date, product, region) and add the measures: Total Sales = [Online Sales] + [Store Sales]. A UNION calculated table also works, but it duplicates data and is rarely better than appending upstream.

31. How do you create a dynamic chart title? Write a measure that reads the selection, and bind it to the title with Format > Title > fx:

DAX
Chart Title =
"Sales for " & SELECTEDVALUE ( Region[Name], "all regions" )
    & " in " & SELECTEDVALUE ( 'Date'[Year], "all years" )

SELECTEDVALUE returns its second argument when zero or several values are selected. CONCATENATEX handles lists (DAX Text Functions).

32. Regional managers must see only their own region. How? Use dynamic row-level security. Create a small Security table with columns Email and Region, relate it to the region dimension, and define a role whose filter on Security is [Email] = USERPRINCIPALNAME ( ). Test with View as in Desktop, publish, then add each manager to the role in the service. People with edit rights in the workspace bypass RLS, so give managers the Viewer role or an app (Row-Level Security).

33. A report is slow in the browser but fine in Desktop. What do you check? Start with Q23, then look at what changed: Performance Analyzer in the service (open the report with the performance pane), the number of visuals on the page, a gateway or DirectQuery source that is slower than local, the capacity load, and RLS expressions that run for every query. Typical fixes are fewer or lighter visuals, an Import model with aggregations, removing unused columns, and simplifying measures with variables (Debugging and Optimizing DAX).

34. Describe the steps to set up incremental refresh.

  1. In Power Query create two Date/Time parameters named exactly RangeStart and RangeEnd.
  2. Filter the table's date column: >= RangeStart and < RangeEnd (only one end inclusive, so no row is loaded twice).
  3. Make sure the filter folds to the source (Query Folding and Incremental Refresh).
  4. Right-click the table in Desktop, choose Incremental refresh, and set how much history to store (for example 5 years) and how much to refresh (for example the last 7 days). Optionally detect data changes and refresh complete periods only.
  5. Publish. The first refresh in the service loads all partitions, and later refreshes reload only the recent ones. It needs a Pro, PPU or capacity license.
Python Stand-in: growth, percent of total, cumulative and second highest

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The last two lines show why the tie question matters. Trail and Pro are both 1,100, so TOPN(2, ...) keeps both and the "second highest" is 1,100. If the interviewer means the second highest distinct value, the answer is 900. Say which one you are computing.

More short questions

35. What are the major components of Power BI, and what does each do? Desktop (connect, transform, model, design), the service (publish, refresh, share, secure), mobile apps (read and get alerts), Report Builder (paginated reports), Report Server (on-premises hosting) and the gateway (access to on-premises data). Power Query, the data model and DAX live inside Desktop. See Self-Service BI and Business Intelligence Concepts.

36. What are the three fundamental concepts of DAX? Syntax (how a formula is written), functions (the pre-built calculations) and context. Context is the one that decides results: row context (the current row inside a calculated column or iterator) and filter context (the filters from slicers, visuals and CALCULATE). See CALCULATE, Filter Context and Row Context.

37. What is the benefit of variables in DAX? A VAR is calculated once and reused, so a formula that needs the same sub-expression three times is faster, easier to read and easier to debug, because you can RETURN the variable to inspect it. A variable is evaluated in the context where it is defined, not where it is used, which is also a common source of surprises.

38. How do UNION and SUMMARIZE differ from appending and grouping in Power Query? UNION(Table1, Table2) stacks rows of two tables with the same number of columns into a calculated table or a virtual table inside a measure, like UNION ALL in SQL. SUMMARIZE(Table, GroupByColumn, "NewCol", Expression) groups by the columns you name and calculates a new column per group. They are evaluated when the model refreshes (calculated tables) or when the visual runs (virtual tables). Power Query's Append and Group By do the same shaping earlier, once, at load time, which is usually better for the model's size and speed. See DAX Table Functions.

39. What do DATESMTD, DATESQTD and DATESYTD return? Tables of the dates from the start of the month, quarter or year up to the last date in the current filter context. TOTALMTD, TOTALQTD and TOTALYTD are shortcuts that evaluate an expression over those dates. All of them need a proper date table marked as the date table (Time Intelligence).

40. What is a drill-through filter? A page-level setup that lets a reader right-click a data point and jump to a detail page already filtered to that item. You create a page, drop the field (for example Customer) into the Drill-through well of that page, and Power BI adds a Back button automatically. See Drill-through, Tooltips, Bookmarks and Buttons.

41. What does a Fabric (F-SKU) capacity change? A Fabric capacity is a purchasable block of compute (F2 up to F2048) that pays for all Fabric workloads, including Power BI, and is the model that replaces the older Premium P-SKU capacities for new purchases. Workspaces assigned to a capacity can use features that need it (large models, paginated reports, deployment pipelines), and from the F64 size upward free-licensed users can view content. Check Microsoft's current licensing page before quoting limits.

Two ideas to demonstrate

Candidates often confuse a calculated column with a measure. The stand-in below computes "share of total" both ways. The column is calculated once, for all the data, so it does not change when a filter changes; the measure is recalculated for the selection.

Python Stand-in: column versus measure

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The column still divides by the grand total of all five rows, so the two selected regions sum to 80 percent. The measure divides by the selected total, so the visible rows sum to 100 percent. When "percent of what the user sees" is the requirement, use a measure.

The next stand-in shows context transition: summing inside a row context. Without it, every row would see the grand total; with CALCULATE, the row's customer becomes a filter.

Python Stand-in: context transition

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In DAX, the second column is CALCULATE ( SUM ( Sales[Amount] ) ), or equivalently a reference to a measure such as [Total Sales]. The measure reference carries an implicit CALCULATE, which is why measures behave correctly inside calculated columns and iterators.

Tips for the interview

  • Say the grain and the filter direction out loud when you describe a model.
  • When asked why a number is wrong, check relationships, filters and the grain before you rewrite the formula.
  • Mention a source of truth you checked against, such as a SQL query or the finance system.
  • Admit what you would test. "I would confirm with Performance Analyzer" beats a guess.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A measure is used inside a calculated column and returns each row's own total instead of the grand total. What explains this?

    Evaluating a measure (an implicit CALCULATE) inside a row context converts the current row into filters, so the result is for that row.
  2. 2. Which option best fits 'show every region's share of what the user has currently selected'?

    ALLSELECTED keeps the filters from outside the visual, such as slicers, so the denominator is the visible total. A calculated column cannot react to the slicer.
  3. 3. An orders table has an order date and a ship date. What is the recommended model?

    A role-playing dimension reuses one date table. The inactive relationship is activated for one measure with USERELATIONSHIP.
  4. 4. What are the two parameters that incremental refresh requires?

    Power BI uses the reserved parameter names RangeStart and RangeEnd, of type date/time, to partition the data and refresh only the recent partitions.
  5. 5. Who is subject to row-level security in a workspace?

    RLS is enforced for users who view the content through the Viewer role or an app. Admins, Members and Contributors can see all the data of the model.

Summary

  • Interviews follow model, Power Query, DAX and service; explain trade-offs and the way you find a wrong number.
  • Star schema, one-to-many relationships with unique keys, a clear grain and role-playing dates are the core modelling answers.
  • Query folding, reference queries, unpivot and incremental refresh (RangeStart and RangeEnd) are the core Power Query answers.
  • Filter context, row context, CALCULATE and context transition explain most DAX behaviour; measures react to filters, calculated columns do not.
  • Roles, apps and row-level security control access; gateways connect on-premises sources for refresh.
  • Scenario answers follow one shape: the idea, the DAX or the steps, and a caution (ties, blanks, filters, licenses).

Next, keep the essentials in one place with the Power BI Cheat Sheet.