Power BI Interview Questions (2026): Most Asked, With Answers

Desktop is the free Windows app where you connect to data, build the model, write DAX, and design report pages, all running locally on your own machine. Service (app.powerbi.com) is the cloud side: publish a.pbix from Desktop and Service handles sharing, workspaces, dashboards, scheduled refresh, and assigning Row-Level Security roles to real users. Neither one talks to your on-prem network by default. The Gateway is separate software installed on a machine inside that network, and it's the only piece of the three that lets cloud-hosted Service reach a SQL Server sitting behind a corporate firewall.

Desktop can refresh against that same SQL Server just fine on its own, since it's already running inside the network. Service can't, which is the entire reason the Gateway exists.

Power Query, the M language underneath, shapes data before it loads: filtering rows, splitting columns, merging tables, fixing types, all running at refresh time. DAX runs after the data's already sitting in the model, at query time, whenever a visual or a measure asks for a number.

Short version: Power Query answers what the data looks like once it's loaded. DAX answers what number you compute from data that's already there. Fix a data quality problem in Power Query and it's fixed for every measure downstream. Try to patch the same problem with DAX instead, and you're usually just working around something that shouldn't have made it into the model.

A small number of fact tables holding the numbers you measure (orders, sales, clicks) sit at the center, surrounded by dimension tables holding the things you filter and group by (customers, products, dates), each dimension connected to the fact table by a single relationship. Microsoft's own modeling guidance puts it plainly: dimension tables enable filtering and grouping, fact tables enable summarization, and a well-designed model gives you tables built for each job instead of one flat table trying to do both (Microsoft Learn, Understand star schema).

That split isn't just a modeling nicety. DAX's filter propagation, and the VertiPaq storage engine underneath the whole model, are both built assuming this shape.

Cardinality describes how many rows on each side of a relationship can match a row on the other side. Most model relationships are one-to-many, one row in a dimension (one customer) matching many rows in a fact table (that customer's orders). Cross-filter direction controls which way a filter travels once applied: single means a filter on the dimension reaches the fact table but not the other way around, both lets a filter on either side reach the other.

Single direction, dimension filtering fact, is the default for a reason. It matches how a star schema is actually meant to work.

A calculated column is computed once per row, at refresh time, and its result is physically stored in the model, adding to file size the same as any other column. A measure is computed at query time, whenever a visual or a card asks for it, using whatever filter context that visual currently has, and it's never stored, the number gets recalculated on demand.

Rule of thumb that holds up in practice: if you need to put the value on an axis, in a slicer, or group by it, it has to be a column, DAX measures can't be sliced or filtered on directly. If it's an aggregation that should respond to whatever's being viewed, total sales for whatever's currently filtered, it should be a measure. Building Total Sales as a calculated column instead of a measure is one of the more common early mistakes. It locks the number to whatever context existed at refresh time instead of the report's actual current view.

By default, clicking a data point in one visual cross-filters or cross-highlights every other visual on that page it's related to, a bar chart click narrows a table below it automatically. Edit Interactions, on the Format ribbon, lets you set how each individual visual responds to a selection in another one, filter, highlight, or none, and that setting is per pair of visuals, not a single global toggle for the whole page.

RLS restricts which rows of data a given user can see in a report, enforced at the model level rather than hidden per visual, so it can't be bypassed by building a new report against the same dataset (Microsoft Learn, Row-level security with Power BI). Roles get authored in Desktop, Modeling > Manage Roles, each one a DAX filter expression applied to a table.

Desktop can only author and test roles, using View As to preview what a role sees. Assigning actual people, or security groups, to those roles has to happen after publishing, in Service, under the dataset's security settings.

Eight scheduled refreshes a day on shared capacity, the tier every Power BI Pro license runs on by default. Move that same dataset onto Premium capacity, Premium Per User, or a Fabric capacity, and the ceiling jumps to forty-eight scheduled refreshes a day (Microsoft Learn, Data refresh in Power BI). The quota resets at 12:01 AM in whatever local time zone the dataset's settings specify, not UTC.

DirectQuery datasets sidestep this limit almost entirely, since they query the source live instead of refreshing a stored copy, but that trades refresh-frequency limits for a different kind of pressure, live query latency on every single visual interaction instead of a batch job.

A dataflow is Power Query logic that runs in the Power BI Service itself, independent of any specific report or Power BI Desktop file. You build the same M queries you'd build locally, joins, cleaning steps, transformations, but they execute on the service's own compute and land as entities in Azure Data Lake Storage or Dataverse. A dataset, by contrast, is the actual data model, tables loaded into VertiPaq with relationships and measures, the semantic layer reports query against.

The practical reason teams use dataflows is reuse. If five report authors each build their own Power Query steps to clean the same Salesforce export, you end up with five slightly different versions of "clean sales data" and five refresh schedules hitting the source. Put that logic in one dataflow, and every dataset that needs it references the dataflow's output as a source, so the cleaning logic lives in one place and refreshes once.

Marking a table as a date table tells the engine two things: which column is the real date key, and that the table has exactly one row per calendar day with no gaps and no duplicates. Functions like SAMEPERIODLASTYEAR, TOTALYTD, and DATEADD don't scan your fact table for dates, they walk the marked table's date column and assume it's contiguous. If a day is missing, those functions silently return the wrong period instead of throwing an error.

Without marking a table yourself, Power BI actually builds a hidden auto date/time table behind any date or datetime column in the model, and that hidden table is what breaks continuity, since it's scoped per column rather than one shared calendar. Marking your own explicit calendar table and disabling auto date/time is really the first thing to do in any real model.

SUM() is a plain aggregator, it takes one column and adds up whatever's in the current filter context, full stop. SUMX() is an iterator: it walks row by row over a table you give it, evaluates an expression per row, then adds up the results. You reach for SUMX when the number you want doesn't exist as a column yet, it has to be computed per row first, quantity times unit price for a line-level revenue total is the classic example.

The gotcha is that SUMX(Sales, Sales[Quantity] * Sales[Price]) is not the same as SUM(Sales[Quantity]) times SUM(Sales[Price]). The iterator multiplies within each row before summing, so it respects the real line-level math. Multiplying two totals afterward just gives you a meaningless number unless every row happens to share the same price.

A bookmark captures the exact state of a report page at the moment you create it, which filters and slicers are set, which visuals are visible or hidden, the current sort order, even scroll position. Clicking that bookmark later snaps the page back to that saved state.

The realistic use is building a guided narrative or a fake navigation menu without writing any code, a "Q1 view" button that jumps to a bookmark with the quarter filter pre-applied, or toggling between a chart and a table of the same data by layering two visuals and swapping their visibility across two bookmarks. It's also how people fake tabs inside one report page, since actual page navigation looks clunkier for that case.

Drill down stays inside one visual and moves through a hierarchy you've defined on an axis, Year to Quarter to Month for example. Clicking the down-arrow on a bar chart expands the same chart into the next level of detail, carrying the same filter context forward.

Drillthrough is a page-to-page jump. You right-click a specific data point, a single customer or product, and it takes you to a separate page configured to accept that value as an incoming filter, showing detail that would be too much to fit into the summary visual. Drill down needs a hierarchy on the visual, drillthrough needs a target page with the drillthrough field sitting in its filter well.

Analyze in Excel connects a live PivotTable in Excel directly to a published Power BI dataset, measures and relationships included, without pulling a static copy of the data. Whoever opens the file gets a real PivotTable they can slice and rebuild the layout of, but the underlying numbers still come from the same governed dataset every other report uses.

People reach for it when they need pivot-table-style ad hoc exploration that Power BI's visual canvas doesn't do well, dragging fields in and out quickly to eyeball a number, or when finance specifically needs the output in Excel for further modeling without maintaining a separate export that risks drifting out of sync with the dataset.

Both are per-user licenses, but PPU unlocks a chunk of Premium-only capability without the org needing to buy a shared Premium capacity: larger dataset sizes, 100GB instead of 1GB, more frequent refreshes, paginated reports, dataflow features like computed entities, and deployment pipelines.

The catch is that everyone who needs to view a report built with PPU features also needs a PPU license themselves, Pro doesn't let you view PPU content. That makes PPU worth it mainly for a smaller team that all needs the bigger features but isn't ready to justify a full Premium capacity, which licenses viewing by capacity rather than by seat, for the whole company.

Desktop authenticates using whatever Windows session or cached credentials already sit on your laptop, no gateway involved, so a refresh there just works. Once the model lives in Service, the cloud has no path into your network at all unless a Gateway is registered and a data source connection is configured on that Gateway with its own, separate set of credentials.

Mismatch is almost always the actual cause. Someone sets up the Gateway connection with a service account, and it doesn't have access to the same database the developer's personal Windows login could see fine from Desktop. Sounds obvious once you say it out loud. Costs people twenty minutes in a real interview when they haven't thought about it before.

Staging. A query with Enable Load turned off still runs its transformation steps and can be referenced by other queries, it just never lands its own copy of rows in the model, so it costs no memory. A common pattern: one "raw" query pulls the source and does light cleanup, three other queries reference it and each build something different, a fact table, a dimension, a lookup, and only those three actually load. Deleting the raw query instead would break all three at once.

Two problems stack on top of each other. First, storage: VertiPaq compresses columns using dictionary encoding, and a low-cardinality column (a handful of distinct categories repeated across a million rows) compresses beautifully, while a customer-name text column repeated on every order line compresses far worse than the same name stored once in a Customer dimension and referenced by a small integer key. Second, DAX: filter propagation is built around dimension-to-fact relationships, and a flat table with none forces every calculation to fake that structure with FILTER and nested table expressions instead of a clean CALCULATE against a real relationship.

The fix is almost always splitting that flat table into a fact table, the order lines, keyed to short integer keys, plus separate dimension tables for Customer, Product, and Date, built in Power Query before the model ever sees it.

Bidirectional filtering between two tables that both sit at the fact-table grain can open more than one path for a filter to travel between them. Once two paths exist, Power BI either throws a circular-dependency error outright or, worse, resolves it in a way that produces a technically-not-wrong but not-what-anyone-meant number. Microsoft's own guidance generally steers people away from bidirectional relationships except in specific bridge-table, many-to-many scenarios.

My preference: leave every relationship single-direction, dimension to fact, and if one specific measure genuinely needs the reverse, handle it inside that one measure with CALCULATE and CROSSFILTER rather than flipping the relationship setting for the whole model. That way the one weird measure is obviously weird, instead of the whole model quietly being weird.

Row context is the "current row" DAX is working through. It exists automatically inside a calculated column, one row at a time, top to bottom, and inside iterator functions like SUMX or FILTER. Filter context is the set of filters currently narrowing what the model can see, from slicers, visual axes, page filters, or CALCULATE.

A calculated column has row context but no filter context by default. Revenue = Price times Qty inside a calculated column just multiplies that one row's own two values, nothing filters it. A measure like SUM(Sales[Amount]) has filter context from whatever visual it's sitting in, but no row context of its own, which is exactly why SUMX exists: it manually creates a row context over a table expression, then evaluates something per row inside that manufactured context, letting a measure do row-by-row math a plain aggregation function can't.

The requirement first, since it's what actually trips people up: SAMEPERIODLASTYEAR, and every built-in time intelligence function, needs a real Date table marked as a date table (Modeling > Mark as Date Table in Desktop), with one contiguous row per calendar day and no gaps, related to the fact table on a proper date column. Run time intelligence against a fact table's own transaction-date column directly, with no separate marked Date table, and it either errors or silently returns wrong numbers.

Sales PY =
CALCULATE(
  SUM(Sales[Amount]),
  SAMEPERIODLASTYEAR('Date'[Date])
)

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

DIVIDE instead of a plain slash matters here too. It returns BLANK, or an alternate value you specify, instead of throwing a divide-by-zero error when last year's number is zero or missing.

SAMEPERIODLASTYEAR is a specific shortcut, always exactly one year back, nothing else. DATEADD is general purpose, you name how many units and which interval, day, month, quarter, or year, so it can give you last month instead of last year, something SAMEPERIODLASTYEAR simply can't do. PARALLELPERIOD is close to DATEADD but returns whole periods rather than shifting day by day: DATEADD shifts every date back roughly thirty days, while PARALLELPERIOD returns every date in the entire previous calendar month regardless of which specific days were in the original selection.

Sales Last Month (DATEADD) =
CALCULATE(SUM(Sales[Amount]), DATEADD('Date'[Date], -1, MONTH))

Sales Prior Month (PARALLELPERIOD) =
CALCULATE(SUM(Sales[Amount]), PARALLELPERIOD('Date'[Date], -1, MONTH))

In practice, most people default to SAMEPERIODLASTYEAR because year-over-year is the most common ask, reach for DATEADD the first time someone wants month-over-month instead, and only bump into PARALLELPERIOD's whole-period behavior once a month-over-month measure gives a slightly odd number on a partial month.

Sync Slicers (View > Sync Slicers pane). It lets one slicer's selection apply across multiple pages, and you can choose per page whether the slicer is also visible there or just applies invisibly in the background.

Copy-pasting the slicer instead creates separate, independent visual instances, each one holding its own selection state, so a user picking a different value on page 2's copy doesn't touch page 1's at all. They can drift out of sync in a way that confuses whoever's looking at the report and can't tell why two pages show different numbers for what looks like the same filter.

Static RLS hardcodes a value into the role's filter, Region equals "West", and you'd need a near-identical role per rep or per region, edited by hand every time someone's territory changes or a new hire joins. Dynamic RLS uses USERPRINCIPALNAME(), or the older USERNAME(), inside the filter expression, compared against a column mapping login identities to whatever they're allowed to see, so one single role auto-filters differently depending on who's actually logged in:

[Email] = USERPRINCIPALNAME()

That one role, applied to a Sales Rep table with an Email column, filters every rep to just their own rows without touching the role definition again. Add a new rep to the source table and they're covered automatically. Most real deployments end up here fast. Static roles scale fine for four people and become a maintenance job nobody wants by the fortieth.

Every visual on a page fires its own DAX query the moment that page loads, or the moment any filter on the page changes. Power BI batches and parallelizes these, but they're still all competing for the same formula engine and VertiPaq storage engine underneath one shared dataset, so fifteen simultaneous queries queue up and contend for resources in a way three or four don't.

Splitting the same visuals across three pages means only the active page's handful of visuals query on load, the other two pages' visuals sit dormant until someone actually navigates there. Same total content, meaningfully different load pattern.

DAX query time is how long the engine took to compute and return the result table, the measure's own performance. Visual display time is how long the visual itself took to render whatever data it got back, the charting library drawing pixels, not the calculation.

A small DAX query time with a large display time means the calculation isn't the bottleneck at all, the rendering is, usually because a visual is trying to plot far too many individual data points, or a slow custom visual is doing something expensive in the browser, or a table or matrix is returning thousands of rows for the browser to paint at once. A lot of people run Performance Analyzer, see a slow visual, and go straight to rewriting the measure, when the actual fix is capping the data points a chart plots or paging a large table instead.

Context transition is what happens when a measure reference, or an explicit CALCULATE, gets evaluated inside a row context, and the engine converts that row context into an equivalent filter context. It's the most misunderstood mechanic in DAX because it happens invisibly, any measure call inside an iterator like SUMX, or inside a calculated column, silently wraps itself in CALCULATE.

Concretely, if a calculated column calls a [Total Sales] measure, DAX takes the current row's values, its ProductKey, its CustomerKey, whatever they are, turns them into filters, then evaluates the measure as if you'd sliced a report by that exact row. That's why calculated columns referencing measures often return numbers that look like a whole-table total collapsed onto one row, rather than the row's own value, if you weren't expecting the filtering. Recognizing when you're inside a row context, iterators and calculated columns, versus a filter context, a card visual or a table cell, is what actually predicts whether context transition is about to happen.

ALL(Table) strips every filter off that table, slicers, relationships, everything, giving you the grand total regardless of what else is on the page. ALLEXCEPT(Table, Table[Column]) is the inverse stated positively, it clears every filter except the ones you name, useful when you want percent of category total and need to keep the category filter alive while dropping a color or size slicer.

REMOVEFILTERS is functionally almost identical to ALL used the same way, but it was added specifically so the CALCULATE modifier reads like intent, REMOVEFILTERS(Table) documents "I am removing a filter here" explicitly, versus ALL which historically also doubled as a plain table function passed into other DAX expressions for a full unfiltered table. Most new code defaults to REMOVEFILTERS for clarity, and reaches for ALLEXCEPT specifically when a small named subset of columns needs to survive.

A relationship shows as a dashed line when Power BI can't guarantee uniqueness or a clean single-column join, typically because it's built on a composite key, several columns concatenated together, rather than one true unique key column. Weak relationships still filter correctly, but they can't be used for certain referential integrity assumptions the engine makes for performance, and they run slower at scale because the engine matches composite text keys instead of a single integer key.

You run into these most often when a source system doesn't expose a clean surrogate key and someone builds a merged key column in Power Query, concatenating OrderID and LineNumber as text to relate two tables that don't otherwise share a unique identifier. The fix, where possible, is building an actual integer surrogate key upstream in Power Query rather than living with the composite text join long term.

You don't relate them directly if neither side is unique on that column, Power BI expects the "one" side of a relationship to actually be one, otherwise you get ambiguous fan-out or the relationship won't validate the way you expect. The standard fix is a bridge table: pull the distinct list of account managers into its own single-column table, then relate both fact tables to that bridge table instead of to each other.

This also solves the classic many-to-many scenario where a single sales rep serves multiple regions and a single region has multiple reps, since going fact-to-fact would double count. With the bridge table in the middle and both facts relating many-to-one into it, filtering by manager on the bridge table correctly filters both facts without ambiguity, and it gives you a clean place to hang a slicer.

A table can only have one active relationship to another table at a time, shown as a solid line, but you can add additional inactive relationships, dashed lines meaning "defined but not used by default." The classic case is an Orders table with both an OrderDate and a ShipDate, both wanting to relate to the same Date table. Only one can be active, usually OrderDate, and the ShipDate relationship sits there inactive.

USERELATIONSHIP inside a CALCULATE lets one specific measure temporarily activate the inactive relationship, just for that calculation.

Sales by Ship Date =
CALCULATE (
  [Total Sales],
  USERELATIONSHIP ( Orders[ShipDate], 'Date'[Date] )
)

That gives you sales by ship date without touching the default relationship every other measure in the model relies on. It's scoped to that single CALCULATE call, so it doesn't create ambiguity anywhere else in the report.

Without it, every scheduled refresh reloads the entire table from the source, full history included, which gets slow and expensive once you're dealing with years of transaction data, and it hits the source system harder than it needs to. Incremental refresh partitions the table by date range and defines which partitions get refreshed each run versus which are treated as historical and locked.

A typical setup keeps five years of history but only refreshes the last ten days on each scheduled run, since anything older is assumed final and won't change. Power BI handles the partitioning behind the scenes once you set the RangeStart and RangeEnd parameters and the policy in the incremental refresh settings, so day-to-day refreshes touch a fraction of the rows and finish in a fraction of the time, while historical partitions sit untouched until you explicitly force a full refresh.

Query folding is Power Query translating your M steps back into the source system's native query language, SQL for a database, so the source does the filtering and aggregating instead of Power BI pulling raw rows and doing the work locally. It breaks the moment you introduce a step the source can't express: a custom column using an M function with no SQL equivalent, a data type change the connector can't push down, a merge with a data source of a different type, or certain Table.Buffer combinations used in the wrong order.

Once folding breaks at a step, every step after it also runs locally, since there's nothing left to fold into, so refreshes get dramatically slower and heavier on your machine or gateway. To check, right-click a query step and look for "View Native Query." If it's greyed out, folding has already stopped by that point, and the fix is usually reordering steps so anything foldable, filters, column selection, basic joins, happens before the step that broke it.

Two real reasons, and only one is about readability. VAR names an intermediate result once and lets you reference it multiple times without recalculating it, so if you need a total for both a ratio and a comparison, VAR computes it a single time rather than the engine evaluating that same expensive expression twice.

Ratio to Total =
VAR CurrentTotal = SUM ( Sales[Amount] )
VAR GrandTotal =
  CALCULATE ( SUM ( Sales[Amount] ), ALL ( Sales ) )
RETURN
  DIVIDE ( CurrentTotal, GrandTotal )

The second reason is that variables are evaluated in the outer context they're declared in, before any RETURN logic runs, so a VAR captures the filter context at the point it's defined and stays fixed even if a later CALCULATE inside RETURN would otherwise shift context. That fixed-context behavior matters for things like comparing a current value against the value calculated before a filter change, not just style preference.

By default, Power BI Desktop creates a hidden calendar table behind every date or datetime column in the model, so users get a built-in year, quarter, month hierarchy in the field list without doing anything. That sounds convenient, but each hidden table spans the min to max date of that specific column and gets built for every date column, not shared across them.

On a model with a dozen date columns across several fact tables, that's a dozen redundant calendar tables silently inflating file size and refresh time, and it means time intelligence functions default to using the wrong, disconnected calendar unless you're careful. The fix is turning it off globally in Options, either per file or as the default for new files, and building one real, marked date table that every date column relates to instead.

A field parameter is a small table you generate through the modeling ribbon's New Parameter, Fields option, whose rows reference actual fields in your model, columns or measures, rather than literal values. You bind a visual's axis or values to that parameter instead of a fixed column, then add a slicer bound to the same parameter.

The result is a report where the user can pick, from a slicer, whether a bar chart shows Sales by Region, Sales by Product Category, or Sales by Salesperson, all in the same visual, without you building three separate charts or the user needing edit access. It's the modern replacement for a lot of what people used to fake with bookmarks and overlapping visuals, and it works for swapping measures too, letting a user toggle a chart between Revenue and Margin with one slicer.

RANKX needs a table expression to rank over, and the mistake is passing it a table that's already been filtered down by the current row context, which leaves it ranking against a table of one, so everything comes back rank 1. Writing RANKX(Products, [Total Sales]) inside a matrix broken out by product gets the Products argument implicitly filtered to just the current row's product by the visual's own context, so there's nothing left to rank against.

Product Rank =
RANKX (
  ALL ( Products ),
  [Total Sales]
)

Wrapping the table argument in ALL(Products), or ALLSELECTED if ranking should respect page-level filters but ignore the visual's own row-by-row breakdown, evaluates RANKX against the full set every time. Ties are another gotcha: RANKX defaults to giving tied values the same rank and skipping the next number, so two products tied for rank 3 both show 3 and the next one jumps to 5, unless you pass the SKIP or DENSE parameter to change that.

Power BI lets individual tables in the same model use different storage modes, so a big transactional fact table can stay DirectQuery, always live against the source, while small dimension tables, a few hundred rows of product or region, get imported for speed. There's also a third mode, Dual, where a table is imported into memory but can also be queried live depending on which mode answers faster for a given query, Power BI picks automatically at query time.

The tradeoff is that relationships between an Import table and a DirectQuery table force the whole query down to the slower storage mode's engine for that calculation, so putting a giant dimension in Import doesn't help much if it constantly has to join against a DirectQuery fact table for every visual. Composite models genuinely help when combining a live, huge, frequently changing fact table with small, mostly static dimensions that benefit from being fast and always available, but they're not a free way to make DirectQuery fast everywhere.

A calculation group lets you define a reusable calculation, year-over-year, prior period, percent of total, once, as a calculation item using a DAX expression with SELECTEDMEASURE() as a placeholder, and apply that same logic across every measure in the model without duplicating the formula per measure.

Without one, a model with fifteen base measures that all need a YoY variant means writing fifteen nearly identical CALCULATE and SAMEPERIODLASTYEAR measures, and every time the YoY logic needs a tweak, you're editing fifteen places. A calculation group turns that into one calculation item that applies to whichever measure a user drops into a visual alongside it. It's built and edited through an external tool like Tabular Editor, not Power BI Desktop's native UI, which is one reason a lot of teams still skip them despite the maintenance win.

Power BI Pro datasets are capped at a two-hour refresh duration and a 1GB model size, and once a table grows large enough, or the source query is slow enough, that two-hour ceiling gets hit and the refresh fails partway through rather than just running long. Premium and PPU push the size limit to 100GB and give more headroom on duration, but the underlying source query performance problem doesn't disappear just because the timeout window got bigger.

Before assuming you need a license upgrade, check whether the slowness comes from the model or the source: a query that isn't folding, pulling millions of raw rows through Power Query's engine instead of letting SQL Server aggregate first, will time out regardless of license tier. Incremental refresh, limiting how much of the table actually gets reprocessed each run, usually fixes the real problem faster and cheaper than a licensing upgrade, since most of these timeouts come from reprocessing years of static history that didn't need to be touched again.

Table.NestedJoin does the merge, then Table.ExpandTableColumn pulls out just the one column you actually want instead of every column on the Product side.

let
  Merged = Table.NestedJoin(Sales, {"ProductKey"}, Product, {"ProductKey"}, "ProductData", JoinKind.LeftOuter),
  Expanded = Table.ExpandTableColumn(Merged, "ProductData", {"Category"}, {"Category"})
in
  Expanded

Against a SQL Server source, Power Query tries to push steps like this down as an actual SQL query instead of pulling every row across the network and filtering locally, that's query folding, and it's the single biggest lever for refresh performance on a large source. Right-click any step and check for "View Native Query"; if it's greyed out, folding broke somewhere upstream.

Adding a custom column that calls a function with no SQL equivalent, a lot of the Text and List functions fall into this bucket, usually breaks folding from that step forward. So does Table.Buffer, which deliberately forces the whole table into memory. I've seen a twenty-minute refresh turn into ninety seconds just from moving one custom column to the end of the query so folding survived longer.

Snowflaking splits a dimension further, Product into Product plus Subcategory plus Category as three separate related tables instead of one flat Product dimension with those as columns. It matches how a normalized source database is often structured, and it can save some storage if a category name is genuinely reused across thousands of products.

My honest take: I flatten almost every snowflake into a single dimension table at the Power Query stage now, before it ever loads. Each extra relationship hop is one more thing DAX has to walk to propagate a filter, and one more thing a candidate has to explain correctly under pressure. A merged, denormalized Product dimension with Category and Subcategory as plain columns costs a little more storage and saves a lot of relationship complexity. I don't have a clean number on how much query time that actually saves, it depends heavily on model size, but the modeling story gets simpler every time.

There's no relationship path from Returns to the Product dimension at all, only Orders is related to Product. A star schema connects fact tables to dimensions, never fact tables directly to each other, and Returns skipped that connection entirely. Category, applied as a filter, has no line to walk to reach Returns' rows, so the measure sees zero matching rows and returns blank.

The clean fix is adding a real relationship from Returns to Product on ProductKey, the same way Orders already has one. If that's not possible, say Returns uses a different key structure, TREATAS can fake the relationship inside the measure itself. It takes whatever ProductKey values are currently in context on the Product side (already filtered by Category, since Category lives directly on that table) and applies them as a filter on Returns[ProductKey], even though no physical relationship connects the two tables:

Returns by Category =
CALCULATE(
  SUM(Returns[Amount]),
  TREATAS(
    VALUES(Product[ProductKey]),
    Returns[ProductKey]
  )
)

CALCULATE takes an expression to evaluate plus zero or more filter arguments. Before evaluating that expression, it takes whatever filter context already exists and, for each filter argument touching a column that's already filtered, replaces that column's filter instead of adding to it. That's the part almost everyone gets backwards early on. People assume CALCULATE always ANDs a new condition onto what's already there. It doesn't, by default it overwrites at the column level.

Total Sales EU =
CALCULATE(
  SUM(Sales[Amount]),
  Sales[Region] = "EU"
)

Drop that measure into a visual already sliced to Region = "US" and it still returns the EU number, because Sales[Region] = "EU" replaced the existing filter on that column rather than combining with it. Wrap the filter argument in KEEPFILTERS if you actually want an intersection instead of a replacement.

CALCULATE also does something less obvious called context transition. Called from inside a row context, inside a calculated column or an iterator like SUMX, it converts that row context into an equivalent filter context first, turning the current row's own column values into single-value filters before evaluating. Every measure reference is implicitly wrapped in its own CALCULATE, which is why a measure can behave completely differently depending on whether it's called from a report visual or from inside another calculation.

Mechanically, nothing's broken. SAMEPERIODLASTYEAR('Date'[Date]) for January 2026 looks for January 2025, and if that store has zero rows in the fact table for a month before it existed, CALCULATE finds nothing to sum, DIVIDE has a zero or blank denominator, and the measure returns blank exactly as designed.

It's a data problem wearing a DAX costume. The fix isn't in the measure, it's a decision the report needs to communicate clearly: either suppress that row for stores without a full prior year, a visual-level filter, or an ISBLANK check returning a specific "N/A" instead of a bare blank, or accept blank and make sure whoever reads the report knows blank doesn't mean zero. I've seen this exact gap get misread as "that new store's growth stalled" in an actual business review, when the real story was just that there was no prior year to compare against yet.

RLS is implemented as an ordinary filter context, nothing more, applied automatically whenever that user's session queries the model. Any DAX that explicitly clears filters, ALL(), ALLEXCEPT(), REMOVEFILTERS(), clears RLS's filter right along with whatever slicer or visual filter it was meant to remove, because DAX has no built-in concept of "this particular filter is special, leave it alone."

It's an easy thing to write without thinking. A total-row measure using ALL(Sales) to ignore visual-level filters on purpose also quietly ignores the RLS role. The fix is scoping ALL() to only the specific columns you actually want to unfilter, ALLEXCEPT with the RLS-relevant table excluded, or ALL() on individual columns rather than the whole table, and testing every measure that touches ALL() with View As Role turned on before it ships. Confirming the role filters the base table correctly isn't the whole test.

The Gateway isn't only for batch refresh jobs, it's the only path Service has into an on-prem network at all, for any kind of connectivity. A DirectQuery model against an on-prem SQL Server queries that source live, on nearly every click, every slicer change, every page load, and each one of those live queries still routes through the Gateway the exact same way a nightly Import refresh would.

That's worth saying out loud in an interview, because it's a common misconception. People hear "DirectQuery means always fresh, no refresh job needed" and assume the Gateway drops out of the picture too. It doesn't. If anything, a busy DirectQuery report puts more continuous load through the Gateway than a dataset that refreshes once overnight and serves everything else from an in-memory cache the rest of the day.

A few real reasons show up repeatedly: a genuine near-real-time freshness requirement where even an hourly refresh isn't fast enough, a fact table too large to reasonably fit in VertiPaq's in-memory compressed footprint even accounting for how well it compresses, or a governance rule that data has to stay at rest in the source system rather than get duplicated into Power BI's own storage.

My default leans hard toward Import for almost everything under a few hundred million rows. VertiPaq compression plus a properly built star schema usually beats DirectQuery's live-query latency by a wide margin, and Import gives every DAX time intelligence function full, fast support that DirectQuery sometimes can't push down efficiently to the source at all. Composite models, mixing Import and DirectQuery tables in one dataset, cover a lot of the middle ground now, so it's rarely a strict either-or choice anymore. I don't have solid numbers on how composite models hold up past a few billion rows specifically, that's past what I've dealt with directly.

You build a separate, usually Import mode, table that pre-summarizes the DirectQuery fact table at a coarser grain, daily totals by product and region instead of every individual transaction row. In the Manage Aggregations dialog, you map each column of the aggregation table to the corresponding column and summarization, Sum, Count, GroupBy, on the detail table.

At query time, the engine checks whether the visual's request can be fully satisfied by the aggregation table's grain. If a user is slicing by region and month and the agg table is built at region-month grain or coarser, it answers from the fast, in-memory Import table instead of sending a query to the live DirectQuery source. The moment a visual asks for something finer, an individual transaction ID or a dimension not present in the aggregation, the engine silently falls back to hitting DirectQuery directly for that specific visual. This is invisible to the report author unless they check Performance Analyzer's query type, and it's why a well-designed aggregation table can make a billion-row DirectQuery model feel like Import for the majority of visuals that only need summary-level numbers.

Row-Level Security has no native, Desktop-UI equivalent for object-level security, hiding a whole column or measure from certain users rather than filtering rows. The workaround is external: connect to the dataset with Tabular Editor, or SSMS for a Premium or PPU-hosted model, and use its OLS editor to mark specific columns or measures as not visible to particular roles at the metadata level, then deploy that back into the model.

OLS enforces at the field list and via the model's metadata, so a user assigned to a restricted role won't see the column to drag it into a visual, and any existing visual using that field shows a permission error instead of rendering. But it depends entirely on the model being hosted where the XMLA endpoint is enabled for read and write, which requires Premium capacity or PPU. A plain Pro workspace can't do this, there's no XMLA write access to push the OLS metadata through. It's also brittle, if someone edits the model later through the regular Desktop UI and republishes without preserving the OLS roles, the restriction can silently get wiped.

FILTER(BigTable,...) forces the engine to materialize a row context and scan BigTable row by row, evaluating the boolean condition against every row, before CALCULATE ever applies it as a filter. On forty million rows, that's a full table scan happening for every single cell of every visual using that measure, and it gets worse under context transition if this measure is itself called from inside another iterator.

The first diagnostic step is Performance Analyzer or DAX Studio's Server Timings tab, looking at whether the query spends its time in the Formula Engine, single-threaded, where row-by-row FILTER logic runs, or the Storage Engine, multi-threaded, where simple column filters run. A FILTER wrapping a simple equality check almost always belongs in the Storage Engine instead.

Total Active =
CALCULATE (
  [Total],
  BigTable[Status] = "Active"
)

Writing it this way lets CALCULATE's own filter argument syntax push the condition down as a native, fast column filter rather than a row-by-row Formula Engine scan. The rule of thumb is FILTER is only actually necessary when the condition can't be expressed as a simple column comparison, anything referencing a measure or requiring row-level computed logic. Everything else should be a plain boolean filter argument directly in CALCULATE.

Direct Lake reads Parquet files straight out of OneLake without copying the data into VertiPaq's in-memory format the way Import does, and without sending live queries to a transactional source system for every request the way DirectQuery does. Instead, the engine memory-maps the Parquet files' columns directly, getting Import-like query speed from reading columnar data efficiently, but without the actual refresh step of loading and compressing that data into the model first.

The practical implication is that a Direct Lake dataset can reflect changes in the underlying lakehouse within minutes, since there's no traditional scheduled refresh pushing data in, but it depends entirely on the source data already living in Fabric's OneLake as Delta Parquet. That means it isn't a drop-in replacement for a model pulling from a regular SQL database or SaaS API, those still need Import or DirectQuery. It also has a fallback behavior: if a query needs something Direct Lake can't serve directly, certain complex transformations, or if the dataset gets evicted from memory under capacity pressure, it can fall back to a DirectQuery-style query against the underlying source, which is slower and worth watching for in Premium capacity metrics if performance suddenly degrades.