Power BI interview questions, answered the way you’d say them.
41 questions for Data Analyst roles, Easy to Hard, 14 of them reported by candidates at companies. Say your answer out loud, then open ours. Free, no sign-up.
Write year-to-date sales for India’s April–March financial year
TOTALYTD’s year end defaults to 31 December, so pass 31 March as the year-end argument. How you write that date depends on the file’s locale, "3-31" for US settings and "31-3" where the day comes first, so check one month by hand. With calendar-based time intelligence, still in preview, you pass the calendar instead and need no string.
Open a question to see the answer to say. Each links to its sources, and to its full explanation where we have one.
Power BI Desktop, the service, reports and dashboards: what’s the difference?
Easy
Power BI Desktop is the Windows app for modelling data and building reports; the service is the cloud side, where you publish, share, refresh and secure them. A report is one or more pages on a single semantic model, which is what a dataset is called now. A dashboard is one page of tiles pinned from reports; it exists only in the service, and it can’t be filtered.
DAX, or Data Analysis Expressions, is the formula language behind Power BI semantic models. It goes into measures, calculated columns and calculated tables, as well as row-level security rules, visual calculations and DAX queries. A simple measure looks like Total Sales = SUM ( Sales[SalesAmount] ).
A calculated column is worked out row by row at refresh and saved in the model, so it takes memory; it’s for what you slice or filter by. A measure is worked out at query time for every cell and saves nothing; it’s for anything you aggregate. Rule of thumb: on an axis or in a slicer, a column; in Values, a measure.
SUM adds up a single column. SUMX is an iterator: it evaluates an expression for each row, such as Sales[Quantity] * Sales[UnitPrice], and then adds up the results. The classic wrong answer for revenue is multiplying two SUMs, total quantity times total unit price, instead of iterating with SUMX.
COUNT counts non-blank numbers, dates and strings, and COUNTA also counts TRUE/FALSE values. COUNTROWS counts the rows of a table and is Microsoft’s recommended way to do that. DISTINCTCOUNT counts unique values, BLANK among them, and its subtotals don’t sum to the total: an order spanning two categories counts once in each category and once in the total.
DIVIDE returns BLANK, or an alternate result you supply, when the denominator is zero or BLANK, and blank results drop out of visuals. Microsoft recommends DIVIDE whenever the denominator could be zero, and the / operator for constants. Average order value is a typical case: DIVIDE ( [Total Sales], [Orders] ) shows blank for a store with no orders.
A star schema has fact tables of events you summarise, such as sales or stock, and dimension tables you filter by, such as date, product and store. A snowflake chains dimensions, like Product to Subcategory to Category. Microsoft generally favours one flattened table: fewer tables, shorter filter paths, and hierarchies can’t cross tables.
Cardinality tells you which side of a relationship has unique values: one-to-many, the common case, one-to-one, or many-to-many. Cross-filter direction tells you which way filters travel: Single, outward from the one side, or Both, which can make queries slower and paths ambiguous, so keep it for where it’s needed. And between two tables, only one relationship can be active.
What is Power Query, and should you merge or append?
Easy
Power Query is Power BI’s data-preparation tool, and each step you click is saved as M code. Merge joins queries on matching columns the way a SQL join does, with left outer, right outer, full outer, inner, left anti and right anti options; append stacks queries with the same columns, like UNION ALL. So twelve monthly files get appended, and each store’s region gets merged in.
Both filter the data. A slicer sits on the report page for readers to change, while the Filters pane holds filters scoped to one visual, one page or all pages. I use slicers for everyday choices like region or month, and the pane for fixed rules, such as leaving out test stores.
Drill-down moves down a hierarchy inside one visual, for example from FY to quarter to month. Drill-through jumps to another page, filtered to whatever you right-clicked, such as a detail page for one city. To set it up, add the field to that target page’s drill-through well.
Lead with the reason a visual fits the question, not just its name. A line chart shows a trend, sorted bars compare categories, a stacked bar shows parts of a whole and a scatter relates two measures. For a single number use a card, and for exact figures, a table.
In Power Query, what’s the difference between referencing a query and duplicating it?
Easy
Duplicate copies the query and all its steps into a new, independent query, so later changes to the original don’t reach it. Reference creates a new query that starts from the original’s output, so changes upstream flow through. I reference when several outputs share the same cleaning, such as one cleaned sales query feeding a summary and a detail table, and duplicate when I want to branch off and change earlier steps.
Referencing doesn’t make the source load once: Microsoft notes that each referencing query runs the original’s steps again, so a dataflow can be the better fix for slow refreshes.
Filter context is the full set of filters on a calculation: slicers, the rows and columns of the visual, the Filters pane and CALCULATE’s filters. Row context is the current row, inside calculated columns and iterators, and by itself it filters nothing. That’s why a calculated column in Product with SUM ( Sales[SalesAmount] ) repeats the grand total on every row, while wrapping it in CALCULATE gives each product’s own sales.
CALCULATE evaluates an expression under a modified filter context. On a column with no filter yet, a filter argument adds one; on a column that’s already filtered, it replaces it, so a South measure still shows South under a West slicer; with KEEPFILTERS the two intersect instead, giving blank. Its arguments can be Boolean expressions, tables, often from FILTER, or modifiers such as REMOVEFILTERS, ALL, ALLEXCEPT, USERELATIONSHIP and CROSSFILTER.
REMOVEFILTERS, or ALL used as a filter, wipes filters from columns or a whole table regardless of the slicers. ALLEXCEPT wipes every filter on a table apart from the columns you list, and ALLSELECTED removes the visual’s own filters but respects the slicers, which gives the visual’s total. When you simply mean “remove filters”, Microsoft recommends REMOVEFILTERS.
A share of the visual’s total is DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLSELECTED ( Store ) ) ).
Why do you need a date table, and how do you mark it?
Medium
Classic time intelligence needs a date table marked via Table tools, Mark as date table, with unique dates, no blanks and no gaps. Build it with CALENDAR or Power Query, and turn auto date/time off. Microsoft now recommends calendar-based time intelligence, still in preview in October 2026: you tag columns as year, quarter, month or week, which makes fiscal and week-based calendars work.
Write year-to-date sales for India’s April–March financial year
MediumCode
TOTALYTD’s year end defaults to 31 December, so pass 31 March as the year-end argument. How you write that date depends on the file’s locale, "3-31" for US settings and "31-3" where the day comes first, so check one month by hand. With calendar-based time intelligence, still in preview, you pass the calendar instead and need no string.
Year-on-year growth: SAMEPERIODLASTYEAR or PREVIOUSYEAR?
MediumCode
SAMEPERIODLASTYEAR shifts the dates in context back one year, like DATEADD(dates, -1, YEAR), so October 2026 is compared with October 2025. PREVIOUSYEAR returns the whole of last year, whatever month you’re looking at, so for growth I’d use SAMEPERIODLASTYEAR. Keep last year in a variable and divide with DIVIDE, so a new store with no sales last year shows blank.
Take the last date visible in the cell, clear the date filters and keep every date up to that one, with FILTER ( ALL ( 'Date' ), … ) inside CALCULATE. Using ALLSELECTED instead starts the total from the reader’s first selected date. The newer option is a visual calculation such as RUNNINGSUM, which works on the visual’s own data: easier to write, but you can’t reuse it in other visuals, dashboards or exports.
Running Sales =
VAR LastDateShown = MAX ( 'Date'[Date] )
RETURN
CALCULATE (
[Total Sales],
FILTER ( ALL ( 'Date' ), 'Date'[Date] <= LastDateShown )
)
Running sum = RUNNINGSUM ( [Total Sales] ) -- the visual calculation
Import keeps a copy in memory, as fresh as the last refresh, and suits most models because its queries are fastest. DirectQuery leaves data at the source and queries it for each visual, for data too big to copy or needed live, and Dual acts as either, usually for dimension tables beside DirectQuery facts. Direct Lake loads Delta tables from Fabric’s OneLake into memory and refreshes only metadata, and a composite model mixes modes table by table.
Direct Lake on SQL endpoints can fall back to DirectQuery for SQL views, but Direct Lake on OneLake never does.
Query folding is Power Query turning your steps into the source’s own language, such as SQL, so the source does the work. Do filters, column removals and joins early, and confirm with View Native Query where it’s offered; CSV and Excel files never fold. It matters because incremental refresh depends on it.
Write each role’s DAX filter in Desktop under Modeling, Manage roles, publish, then add people or groups to the role in the service and check it with Test as role. A static rule fixes a value, like [Region] = "South", while a dynamic rule finds the signed-in user with USERPRINCIPALNAME(). Remember that RLS only limits Viewer permissions, multiple roles combine, and it hides rows, whereas object-level security hides columns.
-- Dynamic role, filter on Store
Store[Region]
IN CALCULATETABLE (
VALUES ( UserRegion[Region] ),
UserRegion[Email] = USERPRINCIPALNAME ()
)
Two dates, one active relationship: report sales by ship date
MediumCode
Only one of the two relationships between Sales and Date can be active, so the ShipDate one is inactive, drawn dashed. USERELATIONSHIP switches it on inside CALCULATE for a single measure. One caveat: with row-level security on those tables it can return an error, so secured models use a separate Ship Date table instead.
Sales by Ship Date =
CALCULATE ( [Total Sales], USERELATIONSHIP ( Sales[ShipDate], 'Date'[Date] ) )
Use RANKX: it places the current value among an expression’s values across a table and returns its position. The order defaults to descending and ties default to Skip, giving 1, 2, 2, 4, while Dense gives 1, 2, 2, 3. Rank over ALLSELECTED so it respects the slicers, and use ISINSCOPE to hide the meaningless rank on the total row.
Write a text measure using SELECTEDVALUE(column, alternate), which gives back the value if exactly one is filtered and the alternate if not, then point the visual’s title at it with fx. SELECTEDVALUE is shorthand for IF(HASONEVALUE(…), VALUES(…), …). One more trap: VALUES may return an extra blank row when keys don’t match, and DISTINCT won’t.
Sales Title =
"Sales in " & SELECTEDVALUE ( Store[City], "all cities" )
& ", " & SELECTEDVALUE ( 'Date'[FY], "all years" )
A variable is evaluated once, so the same expression never runs twice; in Microsoft’s year-on-year example, using a variable roughly halved the query time. Variables also make measures easier to read and to debug, since you can return one to inspect it. And they replace the old EARLIER function.
When do you need a gateway, and how often can a model refresh?
Medium
A gateway lets the service reach data it can’t get to on its own, like a SQL Server in your office; cloud sources need none unless combined with one that does, and Microsoft favours the enterprise (standard) gateway over personal mode. Scheduled refresh allows 8 a day on shared capacity and 48 on Premium, PPU or Fabric, with limits of 2 and 5 hours per refresh. Schedules pause after two months without visits.
Workspace roles, apps and sharing: how do they work?
Medium
Admins run the workspace and its people, Members can bring in people at lower roles and let content be reshared, Contributors build and edit content, and Viewers can only look. To share widely, publish an app, a packaged, read-only set of reports, rather than handing out workspace access.
Why does a measure in a calculated column differ from SUM?
Hard
Inside a calculated column, SUM ignores the current row and returns the grand total. A measure reference acts as though it were wrapped in CALCULATE, turning the row into a filter, which is context transition. Because it filters on every column, rows that are exact duplicates each get their combined total, and it’s expensive inside iterators over big tables.
A Top N filter set on the visual ranks over the entire visual rather than region by region, so I’d do it in a measure. In a matrix with Region above Product, TOPN is evaluated in each region’s context, and KEEPFILTERS limits the result to those three products. TOPN returns every row tied for third place, so I add the product name as a second sort to break ties.
Sales Top 3 in Region =
VAR Top3 =
TOPN ( 3, ALLSELECTED ( 'Product'[Product] ), [Total Sales], DESC, 'Product'[Product], ASC )
RETURN
CALCULATE ( [Total Sales], KEEPFILTERS ( Top3 ) )
Customers and accounts are many-to-many. How do you model joint accounts?
HardCase
Follow Microsoft’s own pattern: keep Customer and Account as dimensions, add a bridging table such as AccountCustomer with one row per pair, relate the three one-to-many, and set one relationship to filter in both directions so customer filters get through to Transaction. Don’t relate the two dimensions directly as many-to-many. Point out that customer balances become non-additive, because joint holders share an account, and that CROSSFILTER can switch direction inside one measure to keep the model single-direction.
A report is slow. How do you find and fix the cause?
Hard
Measure first: Performance Analyzer breaks each visual’s time into DAX query, DirectQuery, visual display and other, and the slowest query can be rerun in DAX query view. Next, fix the model by dropping unused columns and rows, cutting granularity and cardinality and removing needless bi-directional filters. Fix the DAX with variables, preferring Boolean filters to FILTER over a whole table, which Microsoft rates as more efficient, and fix the page with fewer visuals.
Set up two Date/Time parameters named RangeStart and RangeEnd, names that are reserved and case-sensitive, use them to filter the fact table, define a policy such as five years kept and ten days refreshed, then publish. The service partitions the table and refreshes only the recent partitions, and the filter has to fold. It works on Pro, PPU, Premium and Embedded, but a real-time DirectQuery partition needs Premium, PPU or Embedded, and afterwards you can’t download the .pbix.
Filter on at least RangeStart and strictly before RangeEnd, so no row lands in two partitions.
Stock doesn’t add up over time. How do you measure it?
HardCode
Stock adds up across products but not over time, because a month’s stock is its last day’s: that’s a semi-additive measure, and it’s a PL-300 skill. LASTNONBLANK picks the last date that has data, so a month still in progress shows a value, while CLOSINGBALANCEMONTH takes the month’s last date. BFSI rounds ask the same thing about account balances.
Ten measures that each need five time views would mean fifty measures. A calculation group writes each view once, with SELECTEDMEASURE() standing in for whatever measure the visual is showing, and you build it in Model view or TMDL view. Creating one switches on “discourage implicit measures”, because its items only apply to explicit measures, and gives every measure the variant data type.
Let readers switch a chart between sales, units and orders
Hard
Use field parameters (Modeling, New parameter, Fields), which create a small table of measures or columns. Put the parameter in a slicer and in the visual, and readers pick what the chart shows, axes included. The limits: implicit measures aren’t supported, you can’t use them as linked fields of a drill-through or tooltip page, and AI visuals and Q&A don’t work with them.
Managers see their own sales and everyone’s below them. How do you set that up?
HardCode
This is dynamic RLS on a hierarchy. Keep the hierarchy in Employee (EmployeeID, ManagerID, Email), turn it into a path column with PATH, and write a role filter that keeps the rows whose path contains the signed-in user’s ID, found with LOOKUPVALUE and USERPRINCIPALNAME(). The filter on Employee then flows on to Sales.
-- Calculated column: "112|14|3" means 3 reports to 14, who reports to 112
Org Path = PATH ( Employee[EmployeeID], Employee[ManagerID] )
PATHCONTAINS ( -- role filter on Employee
Employee[Org Path],
LOOKUPVALUE ( Employee[EmployeeID], Employee[Email], USERPRINCIPALNAME () )
)
Pro, Premium Per User or Fabric capacity: who needs which licence?
Hard
Creating and sharing content in a workspace needs Pro or Premium Per User, and so does viewing it, unless the workspace sits on an F64 or larger Fabric capacity, or a P capacity, where a free licence with the Viewer role is enough. PPU adds Premium features per user, such as 48 refreshes a day and models over 1 GB, but it isn’t a Fabric capacity. P SKUs are being retired in favour of F SKUs.
Content in a PPU workspace needs a PPU licence to view: a Pro licence isn’t enough.
Copilot, visual calculations and DAX query view: what should you know in 2026?
Hard
Copilot needs a paid Fabric capacity, F2 or higher, or Premium, P1 or higher; Pro or PPU on its own isn’t enough. If the capacity sits outside the US and EU data boundaries, as Indian ones do, an admin must allow processing outside the region, and Microsoft warns that unprepared models give generic or inaccurate answers. PL-300’s skills list also covers four Copilot tasks, visual calculations and DAX query view.
Keep it to two minutes in five parts, then take questions. Open with the question and the reader it served, then the data (sources, size, Power Query cleaning) and the model (star schema, date table, key measures). Then cover the design (visual choices, drill-through, who sees what) and the result: what decision it led to, how you verified the numbers and what you’d do differently.
This question decides most second rounds, so practise the two minutes out loud until you need no notes.