Power BI Interview Questions and Answers

Answer

A star schema has one fact table at the centre holding the numeric events, surrounded by dimension tables that describe those events, each joined on a single key with one to many cardinality. A snowflake normalises the dimensions further, so Product joins to Subcategory which joins to Category instead of holding all three columns in one table. Power BI is built for the star.

The VertiPaq engine and the whole DAX filter propagation model assume filters flow from the one side of a relationship down to the many side, and every additional hop in a snowflake adds a relationship the engine has to traverse at query time. So the honest answer is star by default, and snowflake only when the dimension is genuinely huge or genuinely shared across facts. The interviewer is probing whether you can justify a modelling choice rather than repeat a diagram.

The flat single table is the trap they really want you to argue against. It looks fine because the visuals work, but it repeats every descriptive attribute on every fact row, which explodes the dictionary size for high cardinality text columns, it makes slicers slow because a distinct list has to be computed over millions of rows instead of a small dimension, it makes filtering two facts by the same customer impossible without duplicating the whole table, and it makes any hierarchy or role playing date impossible to reuse. A weak answer says a star is faster and stops there. A strong answer says a star is faster because the dimensions are small and highly compressed, and because filter direction becomes unambiguous.

// Star schema in a typical Indian retail model
//
//                 Dim_Date        Dim_Store
//                     |               |
//   Dim_Customer  >  Fact_Sales  <  Dim_Product
//                         |
//                    Dim_Channel
//
// Relationships: every dimension key -> Fact_Sales, one to many,
// single direction (dimension filters fact), never fact filters dimension.
//
// Snowflake variant (avoid unless the dimension is very large):
//   Dim_Category -> Dim_Subcategory -> Dim_Product -> Fact_Sales
//   Two extra hops the engine must traverse on every category level query.

Answer

A fact table records events and holds the numbers you aggregate: quantity, amount, discount, duration. It is tall, it grows over time, and its columns are mostly keys and measures. A dimension describes an entity that exists independently of any event: customer, product, store, date, employee.

It is wide, comparatively short, and its columns are mostly text attributes you slice by. The practical test is simple, if you would ever put the column on an axis or in a slicer it belongs in a dimension, and if you would ever sum or average it, it belongs in a fact. Grain is the harder half of the question.

The grain is the meaning of one row, stated in a sentence: one row per order line per warehouse per day. You establish it by comparing the row count with the distinct count of the candidate key. If order id is not unique, the table is not one row per order, and every measure written against an order level amount column will double count.

Interviewers ask this because the most common production bug in a Power BI model is a fact table joined at the wrong grain, which silently inflates totals rather than raising an error. The fix is to aggregate the finer table to the grain you need in Power Query before it enters the model, or to keep the fine grain and move the coarse measure to its own table. A weak answer describes facts as numbers and dimensions as text and never mentions grain at all.

Answer

Every time intelligence function in DAX operates on a contiguous, complete date column. If you use the OrderDate column sitting inside the fact table, any date on which nothing happened simply does not exist, so a year to date total silently skips missing days, a month with no sales disappears from the axis, and comparisons across two facts with different date columns become impossible. A dedicated Date dimension solves all of that: it is generated as a continuous range with no gaps, it carries the attributes you actually slice by such as financial year, quarter, month name, month number, week and holiday flags, and it is shared by every fact in the model so one slicer filters sales, targets and returns together.

In India this dimension usually carries a fiscal year running April to March alongside the calendar year, which is a detail worth mentioning because it shows you have built for an Indian finance team. Mark as Date Table tells the engine that this table is the model's date dimension, which does two concrete things: it validates that the chosen column is unique, complete and contains no blanks, and it makes DAX remove all other filters on the Date table when a time intelligence function is applied. Without that marking, a filter on Month Name can fight with a year to date calculation and give you a wrong number. Interviewers ask this because candidates who learned Power BI from short videos often rely on auto date time, and the follow up is always to ask you to switch auto date time off and explain why.

Date =
VAR FirstSale = MIN ( Fact_Sales[OrderDate] )
VAR LastSale  = MAX ( Fact_Sales[OrderDate] )
VAR StartFY   = DATE ( YEAR ( FirstSale ) - IF ( MONTH ( FirstSale ) < 4, 1, 0 ), 4, 1 )
VAR EndFY     = DATE ( YEAR ( LastSale )  + IF ( MONTH ( LastSale )  < 4, 0, 1 ), 3, 31 )
RETURN
ADDCOLUMNS (
    CALENDAR ( StartFY, EndFY ),
    "Year",        YEAR ( [Date] ),
    "Month No",    MONTH ( [Date] ),
    "Month",       FORMAT ( [Date], "MMM" ),
    "Quarter",     "Q" & QUARTER ( [Date] ),
    "Fin Year",    "FY" & FORMAT ( IF ( MONTH ( [Date] ) < 4, YEAR ( [Date] ) - 1, YEAR ( [Date] ) ), "0000" )
                    & "-" & FORMAT ( IF ( MONTH ( [Date] ) < 4, YEAR ( [Date] ), YEAR ( [Date] ) + 1 ), "00" ),
    "Fin Month No", IF ( MONTH ( [Date] ) < 4, MONTH ( [Date] ) + 9, MONTH ( [Date] ) - 3 )
)

// Then: Table tools > Mark as date table > Date column = [Date]
// And: File > Options > Data Load > uncheck Auto date/time for new files

Answer

Cardinality describes how the keys match: one to many is the normal dimension to fact relationship, many to one is the same thing described from the other end, one to one is rare and usually means two tables should have been merged, and many to many is a Power BI feature that creates a hidden intermediate table and should be a deliberate choice, not an accident. Cross filter direction controls which way a filter travels. Single means the one side filters the many side, which is what you want almost always: selecting a product filters sales, but selecting a sale does not filter products.

Both, or bidirectional, lets the filter travel back up. The legitimate uses are narrow. The common one is a slicer built on a dimension that should only show values present in the fact, for example, only showing cities that actually have orders.

Another is a bridge table in a genuine many to many design. Outside those, bidirectional filtering causes two real problems. First, ambiguity: once two dimensions can each filter the fact and be filtered back from it, there can be more than one path between two tables, and Power BI will either refuse to activate the relationship or silently pick a path you did not intend, which makes totals unexplainable.

Second, performance, because the engine now has to expand the filter across more tables on every query. The interviewer is checking whether you reach for bidirectional as a quick fix. A weak answer says use both directions when the filter is not working. The better answer is to fix it with CROSSFILTER inside a single measure, so the change is scoped to where it is needed instead of being baked into the model.

// Instead of setting the relationship to Both in the model,
// scope the bidirectional behaviour to one measure only:

Customers Who Bought =
CALCULATE (
    DISTINCTCOUNT ( Dim_Customer[CustomerKey] ),
    CROSSFILTER ( Fact_Sales[CustomerKey], Dim_Customer[CustomerKey], BOTH )
)

// Slicer that only lists cities with at least one order,
// again without touching the relationship:

City Has Sales =
IF ( NOT ISEMPTY ( RELATEDTABLE ( Fact_Sales ) ), 1, 0 )   // calculated column on Dim_City

Answer

This is the role playing dimension question and it comes up in almost every mid level Power BI interview because logistics, insurance and banking models all have it. Power BI allows only one active relationship between any two tables, so you create the relationship on OrderDate as active and a second relationship on ShipDate as inactive, shown as a dotted line in the model view. Nothing filters through the inactive relationship until a measure switches it on with USERELATIONSHIP inside CALCULATE.

That gives you two sets of measures against a single Date dimension: Sales Amount using order date, and Shipped Amount using ship date, both sliceable by the same year, quarter and month columns and by the same slicer. The alternative, which interviewers will ask you to compare, is to import the Date table twice as Order Date and Ship Date with separate active relationships. That is easier for self service users because they can drag the right date directly onto the axis without needing a special measure, but it costs you a second slicer, a second set of hierarchies, and confusion about which date a shared visual is using.

The judgement to state out loud is this: use inactive relationships plus USERELATIONSHIP when a small number of measures need the alternate date, and duplicate the dimension when business users will build their own reports and need both dates as first class fields. A weak answer says you would just add another date column to the visual, which does not work because there is no active path.

// Model: two relationships from Fact_Sales to 'Date'[Date]
//   Fact_Sales[OrderDate] -> active
//   Fact_Sales[ShipDate]  -> inactive

Sales Amount =
SUMX ( Fact_Sales, Fact_Sales[Quantity] * Fact_Sales[UnitPrice] )

Shipped Amount =
CALCULATE (
    [Sales Amount],
    USERELATIONSHIP ( Fact_Sales[ShipDate], 'Date'[Date] )
)

// Days between order and shipment, still one Date table:
Avg Fulfilment Days =
AVERAGEX (
    Fact_Sales,
    DATEDIFF ( Fact_Sales[OrderDate], Fact_Sales[ShipDate], DAY )
)

Answer

A calculated column is evaluated row by row at refresh time and the result is physically stored in the model, so it takes memory, it increases model size, and it is only as fresh as the last refresh. It has row context by default but no filter context from the report, so it cannot respond to a slicer. Use it when you need a value to slice, group, filter or sort by, for example, a price band or a fiscal quarter label.

A measure is not stored at all; it is a formula evaluated at query time against whatever filter context the visual creates, so it costs almost no memory and always reflects the current selection. Use it for every aggregation. A calculated table is a full table materialised at refresh time from a DAX expression, useful for a generated date dimension, a disconnected parameter table or a filtered subset, but it also occupies memory and is recomputed on every refresh.

The rule interviewers want to hear is measure by default, calculated column only when the value must appear on an axis or in a slicer, and calculated table only when the table cannot come from the source. The strong version adds one more layer: if a calculated column can be created upstream in SQL or in Power Query, do it there instead, because a Power Query column compresses better and does not add a DAX dependency chain to the refresh. A weak answer only lists syntax differences and never mentions memory, refresh timing or the fact that calculated columns cannot see slicers.

// Calculated column: stored, refresh time, sliceable
Price Band =
SWITCH (
    TRUE (),
    Fact_Sales[UnitPrice] < 500,  "Under 500",
    Fact_Sales[UnitPrice] < 2000, "500 to 2000",
    "Above 2000"
)

// Measure: not stored, query time, respects every slicer
Total Sales = SUMX ( Fact_Sales, Fact_Sales[Quantity] * Fact_Sales[UnitPrice] )

// Calculated table: materialised at refresh
Top Customers =
TOPN ( 100, SUMMARIZE ( Fact_Sales, Dim_Customer[CustomerKey] ), [Total Sales], DESC )

// This column will NOT react to a slicer, and that surprises people:
Wrong Column = [Total Sales]   // same value repeated on every row

Answer

A true many to many exists when neither side has a unique key for the join, for example, a bank account can have several customers and a customer can hold several accounts, or a sales target is set per region and per product category while the fact records individual products in individual stores. Power BI lets you set the relationship cardinality directly to many to many, which quietly builds a hidden intermediate table of distinct key values. It works, but it has consequences you should be able to name: filters no longer propagate with the simple one direction guarantee, blank rows appear for keys present on one side and not the other, and totals stop equalling the sum of the visible rows because a single fact row can belong to several groups.

The classical alternative is a bridge table, a slim table holding the distinct pairs of keys, joined one to many from each dimension, with the cross filter direction set to both on one leg. That is more code but it is explicit, it makes the ambiguity visible in the model diagram instead of hiding it, and it lets you attach a weighting or allocation column when the business wants revenue split across owners rather than counted fully on each. Interviewers ask this to see whether you understand that many to many is a reporting decision, not just a technical setting. The follow up is usually about totals: you must be able to say that under a many to many the grand total is the correct unduplicated figure and therefore does not match the sum of the rows, and that this is a business conversation before it is a DAX problem.

// Bridge pattern: Dim_Customer  ->  Bridge_AccountCustomer  <-  Dim_Account
//   Bridge holds the distinct pairs: (CustomerKey, AccountKey)
//   Dim_Customer -> Bridge : one to many, single
//   Dim_Account  -> Bridge : one to many, BOTH   (so customer reaches the fact)

Balance (unduplicated) =
CALCULATE (
    SUM ( Fact_Balance[Amount] ),
    CROSSFILTER ( Bridge_AccountCustomer[AccountKey], Dim_Account[AccountKey], BOTH )
)

// With an allocation column on the bridge, joint accounts split instead of double counting:
Balance (allocated) =
SUMX (
    Bridge_AccountCustomer,
    CALCULATE ( SUM ( Fact_Balance[Amount] ) ) * Bridge_AccountCustomer[SharePct]
)

Answer

This is a diagnostic trap question and the panel is watching your order of checks, not your first guess. Work outside in. First, is there a relationship at all between the slicer's table and the table behind the card?

If the slicer sits on a standalone Excel sheet somebody imported, there is no path and no filter. Second, is the relationship active? An inactive dotted relationship will not propagate anything unless a measure calls USERELATIONSHIP.

Third, is the direction right? A single direction relationship only flows from the one side to the many side, so a slicer built on a fact column will not filter a dimension based measure. Fourth, check interactions: Format ribbon, Edit interactions, where the card may be explicitly set to None, which is by far the most common cause when the model looks fine.

Fifth, check whether the measure itself removes the filter, because any measure containing ALL, REMOVEFILTERS or ALLSELECTED over the slicer's column will ignore the selection by design, which is exactly what a percentage of total measure does on purpose. Sixth, check whether the slicer is on a different page and was expected to sync, since sync slicers are opt in per page. Seventh, check whether the card is on a page with a page level or report level filter that overrides the visible selection.

A weak answer jumps straight to 'the relationship must be broken' and stops. A strong answer walks the list and mentions Edit interactions early, because interviewers know that is the real culprit most of the time.

Answer

SUM is a simple aggregator over a single column that has already been materialised in the model, and internally it is just syntax sugar for SUMX over the table with one column reference. SUMX is an iterator: it walks a table row by row, creates a row context for each row, evaluates the expression, and sums the results. The difference matters the moment your expression needs more than one column from the same row.

Revenue calculated as quantity multiplied by unit price cannot be written with SUM, because SUM(Quantity) times SUM(Price) multiplies two totals and gives you a number that is wrong on every row that is not a single transaction. SUMX is unavoidable in three situations: row level arithmetic across columns, aggregating a measure over a grouping table such as SUMX over VALUES of a customer key, and any calculation where the granularity of the sum differs from the granularity of the fact, for example, summing a per product margin measure across a category. AVERAGEX behaves identically for averages, and it also gives you the correct average of a measure rather than an average of an already averaged column, which is a classic reporting bug.

The performance point interviewers listen for is that iterators over a stored column are fully handled by the storage engine and are effectively free, while iterators whose expression calls a measure force context transition per row and can fall back to the slower formula engine. A weak answer says SUMX is used when SUM does not work, without explaining what row context has to do with it.

// WRONG: multiplies two grand totals
Revenue Wrong = SUM ( Fact_Sales[Quantity] ) * SUM ( Fact_Sales[UnitPrice] )

// RIGHT: row by row, then summed
Revenue = SUMX ( Fact_Sales, Fact_Sales[Quantity] * Fact_Sales[UnitPrice] )

// Iterating a grouping table, not the fact:
Avg Revenue per Customer =
AVERAGEX ( VALUES ( Dim_Customer[CustomerKey] ), [Revenue] )

// Margin that must be computed per line before it is summed:
Margin =
SUMX (
    Fact_Sales,
    Fact_Sales[Quantity] * ( Fact_Sales[UnitPrice] - RELATED ( Dim_Product[StandardCost] ) )
)

Answer

Because division by zero and division by blank are extremely common in reporting, and the two operators handle them differently. The slash operator returns Infinity when the numerator is non zero and the denominator is zero, and NaN when both are zero, and those values then propagate into every visual and every downstream measure, producing the ugly Infinity text on cards and breaking conditional formatting and sorting. DIVIDE performs the same division but checks the denominator first and returns BLANK by default, or an alternate result you supply as the optional third argument.

Blank is the value Power BI already knows how to hide, so an empty cell appears instead of a broken one, and rows can be filtered out cleanly. The second reason is performance and it is the one that gets you credit in an interview: DIVIDE is optimised internally by the engine, whereas writing IF ( denominator = 0, BLANK (), numerator / denominator ) evaluates the denominator twice and adds a branch the formula engine must handle, which shows up in a large matrix. So DIVIDE is both safer and faster than the manual guard that people write to avoid the slash.

The one nuance worth naming is that you should not blindly pass 0 as the alternate result, because a zero growth rate and an undefined growth rate mean different things to a business reader, and showing 0 percent for a division that could not be computed is quietly misleading. A weak answer says DIVIDE avoids errors and stops there; there is no error to avoid, the slash does not throw, it returns Infinity, and knowing that distinction is the point.

// Slash: returns Infinity, not an error
Margin Pct Bad = [Margin] / [Revenue]

// Manual guard: evaluates the denominator twice
Margin Pct Verbose = IF ( [Revenue] = 0, BLANK (), [Margin] / [Revenue] )

// Correct and optimised
Margin Pct = DIVIDE ( [Margin], [Revenue] )

// Alternate result only when zero is genuinely meaningful
Conversion Rate = DIVIDE ( [Orders], [Sessions], 0 )

Answer

Variables do three things. First, they evaluate once and reuse the result, so a subexpression referenced three times is computed once rather than three times, which is a genuine and measurable performance win in a large matrix. Second, and this is the part that catches candidates out, a variable is evaluated in the filter context that exists where it is declared, not where it is used.

That makes it the standard, safest way to capture a value before CALCULATE changes the context around it, and it is why the correct year on year pattern stores the current period total in a variable before computing the prior period. It also means a variable inside CALCULATE will not see the modified context, which surprises people and is a favourite follow up question. Third, variables make debugging possible, because you can temporarily point RETURN at any variable to see exactly what that step produces, which is how you isolate which half of a broken measure is wrong.

Variables can hold scalars or entire tables, and naming them properly turns an unreadable nested formula into something a reviewer can follow line by line. The limitation to mention is that a variable is immutable once assigned, so you cannot reassign it in a loop style, and its scope is only the expression it belongs to. Interviewers ask this because measure quality is a proxy for whether you will be a maintenance problem on a client project. A weak answer says variables make code readable and offers nothing about single evaluation or context capture.

Sales YoY % =
VAR CurrentSales = [Total Sales]
VAR PriorSales =
    CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
VAR Delta = CurrentSales - PriorSales
RETURN
    IF (
        NOT ISBLANK ( PriorSales ),
        DIVIDE ( Delta, PriorSales )
    )

// Debugging: point RETURN at one variable to inspect that step
// RETURN PriorSales

// Context capture: MaxDate is evaluated BEFORE CALCULATE changes context
Last Day Sales =
VAR MaxDate = MAX ( 'Date'[Date] )
RETURN CALCULATE ( [Total Sales], 'Date'[Date] = MaxDate )

Answer

RELATED travels from the many side to the one side of an existing active relationship and returns a single scalar value, so from a sales row you can fetch the product category or the customer city. It requires a relationship and it requires row context, which means it works in a calculated column or inside an iterator, not in a bare measure. RELATEDTABLE goes the other way, from the one side to the many side, and returns a table, so from a customer row you get all of that customer's sales rows, which is why it is normally wrapped in COUNTROWS, SUMX or ISEMPTY.

Internally RELATEDTABLE is CALCULATETABLE with context transition, which is a nice detail to drop. LOOKUPVALUE needs no relationship at all: you give it the result column, then pairs of search column and search value, and it scans for a match, optionally with a default when nothing matches. That flexibility costs performance, because there is no relationship for the engine to exploit, and it errors or returns the alternate result when multiple rows match.

The decision rule to state is simple: if a relationship exists, use RELATED or RELATEDTABLE because the engine optimises them; use LOOKUPVALUE only when no relationship exists or when you deliberately do not want one, most commonly in dynamic row level security where you look a user up by email. A weak answer treats all three as interchangeable ways of doing a VLOOKUP, which tells the panel you have not thought about relationships or row context at all.

// Calculated column on Fact_Sales: many side reaching the one side
Product Category = RELATED ( Dim_Product[Category] )

// Calculated column on Dim_Customer: one side reaching the many side
Order Count = COUNTROWS ( RELATEDTABLE ( Fact_Sales ) )

// No relationship needed, and the standard pattern in dynamic RLS
User Region =
LOOKUPVALUE (
    Dim_UserSecurity[Region],
    Dim_UserSecurity[Email], USERPRINCIPALNAME (),
    "Unassigned"
)

Answer

All three answer questions about what is currently selected, but at different levels. HASONEVALUE returns TRUE when exactly one value of a column is visible in the current filter context, and it is the guard you use before you assume a single selection. SELECTEDVALUE is the shorthand for that whole pattern: it returns the single visible value or a fallback you supply when zero or many values are visible, so it replaces IF ( HASONEVALUE ( col ), VALUES ( col ), fallback ).

ISINSCOPE is different in kind. It tells you whether a column is being used as a grouping level in the current visual, which is how you detect where you are inside a matrix hierarchy or on a total row. A subtotal row for Category has Category in scope but not Product, so ISINSCOPE lets you write a measure that behaves differently at each level, for example, showing a rank only at leaf level and a blank on totals.

The classic application interviewers ask for is a dynamic title, which is a text measure bound to the visual title through the conditional formatting fx button. The pattern is to read the slicer selections with SELECTEDVALUE, provide sensible fallbacks such as All India or All Products, and concatenate. A weak answer only mentions SELECTEDVALUE and does not know that a title has to be a measure hooked into the title's fx expression, which is the practical half of the question.

// Dynamic title measure, bound via Format pane > Title > fx > Field value
Report Title =
VAR SelRegion   = SELECTEDVALUE ( Dim_Store[Region], "All India" )
VAR SelCategory = SELECTEDVALUE ( Dim_Product[Category], "All Categories" )
VAR SelYear     = SELECTEDVALUE ( 'Date'[Fin Year], "All Years" )
RETURN
    SelCategory & " sales, " & SelRegion & ", " & SelYear

// Level aware measure inside a matrix
Rank in Level =
IF (
    ISINSCOPE ( Dim_Product[Product] ),
    RANKX ( ALLSELECTED ( Dim_Product[Product] ), [Total Sales], , DESC ),
    BLANK ()
)

// The long form SELECTEDVALUE replaces
Selected Region Long =
IF ( HASONEVALUE ( Dim_Store[Region] ), VALUES ( Dim_Store[Region] ), "All India" )

Answer

RANKX takes a table to rank over, an expression to rank by, an optional value expression, an order, and a ties argument. The three failure modes an interviewer will push on are the table argument, context transition and ties. First, the table argument decides the population you are ranking within.

Passing the raw column gives you a rank within the current filter context, which means every row of a matrix ranks against itself and returns 1. You almost always want ALL or ALLSELECTED over the column so that each row is compared against the full or the visible set, and choosing between them is a real decision: ALL ignores slicers, ALLSELECTED respects them, and business users nearly always mean ALLSELECTED. Second, the expression is evaluated in row context over that table, so it needs context transition to become a filter; using a measure gives you that automatically, while writing a raw column expression does not, which is why a hand written expression can return a flat rank.

Third, ties: the default is Skip, so two products tied at rank 2 are followed by rank 4, and Dense makes the next rank 3. Businesses expect Skip for leaderboards and Dense rarely. The fourth practical problem is totals, because a total row has no single product to rank, and RANKX returns a meaningless number there unless you guard with HASONEVALUE or ISINSCOPE. A weak answer writes RANKX ( ALL ( table ), [measure] ) without explaining what changes if you swap ALL for ALLSELECTED.

Product Rank =
IF (
    HASONEVALUE ( Dim_Product[Product] ),
    RANKX (
        ALLSELECTED ( Dim_Product[Product] ),   // respects slicers; ALL would ignore them
        [Total Sales],
        ,
        DESC,
        DENSE
    )
)

// Top N flag driven by a what if parameter, useful in a practical round
Is Top N =
VAR N = SELECTEDVALUE ( 'TopN'[TopN], 10 )
RETURN IF ( [Product Rank] <= N, 1, 0 )

Answer

Blank in DAX is a deliberate value, not an error, and the panel wants the list of causes in the right order. The most common cause is that the filter context for that row genuinely selects zero fact rows, for example, a product that had no sales in the selected month, so SUM returns blank and Power BI helpfully hides the entire row. That is correct behaviour, and the fix is a business decision about whether to show zero.

The second cause is a broken relationship or a key mismatch, where the fact holds keys the dimension does not, in which case the values land on a blank row rather than under the expected label; you spot it because there is a literal Blank member in the dimension. The third cause is a division that returned BLANK through DIVIDE. The fourth is a filter conflict inside the measure, typically a CALCULATE that filters to a value that cannot coexist with the row's own filter, such as filtering to region West inside a row for region South, which yields an empty intersection.

The fifth is data type or case mismatch on the join key, with trailing spaces in a text key being the classic cause after an Excel import. To force zeros where the business wants them, add 0 to the measure or wrap it in a COALESCE, but understand the cost: a matrix that previously showed 200 rows can suddenly show 20,000 because nothing is blank any more, and performance drops. A weak answer just says use IF ISBLANK and returns 0 without understanding what changed.

// Show a zero instead of blank, but understand what it does to row counts
Total Sales Zero = [Total Sales] + 0
// or
Total Sales Zero2 = COALESCE ( [Total Sales], 0 )

// Only show zero where the product genuinely exists in the catalogue
Total Sales Safe =
IF (
    NOT ISEMPTY ( VALUES ( Dim_Product[Product] ) ),
    COALESCE ( [Total Sales], 0 )
)

// Find key mismatches before blaming DAX:
// a blank row appearing in a dimension means fact keys with no dimension match
Orphan Rows = COUNTROWS ( FILTER ( Fact_Sales, ISBLANK ( RELATED ( Dim_Product[Product] ) ) ) )

Answer

Filter context is the set of filters applied to the model when an expression is evaluated. It comes from the visual's rows and columns, from slicers, from page and report filters, and from CALCULATE. It restricts which rows of which tables are visible.

Row context is different: it is the notion of a current row, and it exists only inside a calculated column or inside an iterator such as SUMX, FILTER or AVERAGEX. Row context lets you read column values, but it does not filter anything by itself, which is the point candidates miss. A calculated column has row context but no report filter context, which is why a calculated column cannot react to a slicer.

Context transition is the bridge between the two: whenever CALCULATE is invoked inside a row context, that row's values are converted into equivalent filters on the model. Every measure reference carries an implicit CALCULATE, so calling a measure inside SUMX silently triggers context transition on each row, and that is exactly why SUMX ( Dim_Customer, [Total Sales] ) gives per customer totals rather than the same grand total repeated. Interviewers probe this because it explains most of the wrong numbers people produce.

The two consequences to name are correctness, since transition is what makes iterating a dimension work, and performance, since transition per row over a large table is expensive and is a leading cause of slow measures. A weak answer defines both contexts correctly and then cannot explain why calling a measure inside an iterator behaves differently from inlining its expression.

// Row context only: reads the row, filters nothing
Line Total = Fact_Sales[Quantity] * Fact_Sales[UnitPrice]   // calculated column

// Iterating a dimension: [Total Sales] carries an implicit CALCULATE,
// so context transition applies each customer as a filter
Customers Above 1 Lakh =
COUNTROWS (
    FILTER (
        VALUES ( Dim_Customer[CustomerKey] ),
        [Total Sales] > 100000
    )
)

// Same idea written explicitly, to show what the implicit CALCULATE does
Customers Above 1 Lakh Explicit =
COUNTROWS (
    FILTER (
        VALUES ( Dim_Customer[CustomerKey] ),
        CALCULATE ( SUM ( Fact_Sales[Amount] ) ) > 100000
    )
)

Answer

CALCULATE evaluates an expression in a filter context that it modifies with the filter arguments you give it. The order of operations matters and interviewers ask for it. First, the filter arguments are evaluated in the original, outer filter context, not in the modified one.

Second, if CALCULATE is running inside a row context, context transition happens, converting the current row into filters. Third, the filter arguments are applied on top, and by default each argument replaces any existing filter on the columns it touches rather than intersecting with it. Fourth, the expression is evaluated in that new context.

The replace rather than intersect default is the single most misunderstood part, and it is why CALCULATE ( [Sales], Dim_Product[Category] = 'Beverages' ) returns Beverages sales even inside a row for Snacks: the row's own category filter on that column is overwritten. KEEPFILTERS is what changes that to an intersection. CALCULATE is called the most important function because it is the only way to change filter context, and because everything else that appears to change context is CALCULATE in disguise: CALCULATETABLE is the table returning version, every measure reference is an implicit CALCULATE, RELATEDTABLE is CALCULATETABLE with transition, and all time intelligence functions are shorthand for CALCULATE with a date filter. A weak answer describes CALCULATE as a way to apply a filter to a measure and never mentions that filter arguments are evaluated in the outer context or that filters replace rather than add.

// Filters REPLACE the existing filter on the same column
Beverages Sales =
CALCULATE ( [Total Sales], Dim_Product[Category] = "Beverages" )
// In a matrix row for Snacks, this still returns Beverages sales.

// Remove a filter instead of setting one
Sales All Products =
CALCULATE ( [Total Sales], REMOVEFILTERS ( Dim_Product ) )

// Percentage of category: numerator in context, denominator with product filter removed
Pct of Category =
DIVIDE (
    [Total Sales],
    CALCULATE ( [Total Sales], REMOVEFILTERS ( Dim_Product[Product] ) )
)

// Time intelligence is CALCULATE in disguise
Sales YTD Long =
CALCULATE ( [Total Sales], DATESYTD ( 'Date'[Date] ) )

Answer

A boolean filter argument such as Dim_Product[Category] = 'Beverages' is syntax sugar. The engine rewrites it internally as FILTER ( ALL ( Dim_Product[Category] ), Dim_Product[Category] = 'Beverages' ). Two things follow from that expansion.

First, it removes the existing filter on that one column, because of the ALL, which is why it replaces rather than intersects. Second, it operates on a single column, so it is cheap and can usually be pushed down to the storage engine and resolved as a simple scan. FILTER written explicitly is an iterator over a table you choose, evaluating a row by row condition, and it can therefore express conditions the boolean form cannot: comparisons between two columns, conditions on measures, and multi column logic.

The cost is that FILTER over a whole fact table materialises a potentially huge intermediate table and is far more likely to be handled by the slower formula engine. The nuance the question is really testing is the table argument. FILTER ( Dim_Product, ... ) filters within the current context, so it intersects with what is already selected.

FILTER ( ALL ( Dim_Product ), ... ) ignores the current selection entirely on that table and can therefore return values outside the visual's own row, which is exactly how you build a comparison against the whole population. So they are not stylistic alternatives; they answer different questions. The rule to give is: use the boolean form for simple single column conditions, use FILTER over a dimension when you need measure based or multi column logic, and never use FILTER over a large fact table when a boolean predicate on a dimension would do.

// These two are identical; the first is sugar for the second
A = CALCULATE ( [Total Sales], Dim_Product[Category] = "Beverages" )
B = CALCULATE ( [Total Sales], FILTER ( ALL ( Dim_Product[Category] ), Dim_Product[Category] = "Beverages" ) )

// Intersects with the current selection (stays inside the visual's row)
C = CALCULATE ( [Total Sales], FILTER ( Dim_Product, Dim_Product[Price] > 1000 ) )

// Ignores the current product selection entirely (whole catalogue)
D = CALCULATE ( [Total Sales], FILTER ( ALL ( Dim_Product ), Dim_Product[Price] > 1000 ) )

// Only FILTER can express a measure based condition
Sales from Big Customers =
CALCULATE (
    [Total Sales],
    FILTER ( VALUES ( Dim_Customer[CustomerKey] ), [Total Sales] > 500000 )
)

// Avoid: iterating the fact table when a dimension predicate would do
Slow = CALCULATE ( [Total Sales], FILTER ( Fact_Sales, RELATED ( Dim_Product[Category] ) = "Beverages" ) )

Answer

ALL removes filters from a table or from listed columns and, when used as a table expression, returns the unfiltered rows. REMOVEFILTERS does the same filter removal but only ever acts as a filter modifier, never as a table returning function, so it is the clearer choice inside CALCULATE and is what Microsoft now recommends for that purpose. ALLEXCEPT removes filters from a table except on the columns you name, which is how you build a subtotal that respects a grouping level, for example, sales for the whole category regardless of which product is selected.

ALLSELECTED is the awkward one and the one interviewers use to separate levels. It restores the filter context as it was outside the current visual, which in practice means it respects slicers and page filters but ignores the row or column grouping of the visual itself. That is precisely what a business user means by percentage of visible total.

The trap is that ALLSELECTED behaves differently depending on where it is called and is notoriously hard to reason about in nested contexts, so the professional habit is to use it deliberately for visual level totals and not scatter it through a model. The concrete comparison to walk through is a matrix of product by month with a region slicer set to South. ALL gives you the percentage of all sales ever recorded, ignoring the South slicer.

ALLSELECTED gives you the percentage of South sales, which is what the user expects. ALLEXCEPT over the category column gives the percentage within the product's own category. A weak answer describes ALL and ALLSELECTED as the same with a small difference and cannot say which respects a slicer.

// Denominator ignores every filter, including the region slicer
Pct of Grand Total = DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALL ( Fact_Sales ) ) )

// Denominator respects slicers, ignores the visual's own rows: what users expect
Pct of Visible Total =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLSELECTED ( Dim_Product[Product] ) ) )

// Denominator is the product's own category total
Pct of Category =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLEXCEPT ( Dim_Product, Dim_Product[Category] ) ) )

// Preferred modern syntax inside CALCULATE
Pct of All Products =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], REMOVEFILTERS ( Dim_Product ) ) )

Answer

KEEPFILTERS changes CALCULATE's default behaviour from replace to intersect. Normally a filter argument wipes the existing filter on the columns it touches, so a measure that filters to high value orders inside a matrix sliced by order size band will return the same number in every band, because the band filter on that column has been overwritten. Wrapping the filter argument in KEEPFILTERS keeps the outer filter and intersects the new condition with it, so the row for the band under one thousand rupees correctly returns blank when the inner condition asks for orders above five thousand.

The clean way to describe it is that KEEPFILTERS makes CALCULATE behave the way most people already assume it behaves. It matters most in three places: measures written for a matrix where a slicer or row header filters the same column the measure filters, calculations that layer several conditions on the same column, and time intelligence variants where you want to keep an existing month filter while adding a year to date window. It also appears inside SUMMARIZECOLUMNS filters generated by the visual layer, which is why the same measure sometimes behaves differently in a card and in a matrix.

Interviewers ask this at senior level because it demonstrates you have actually debugged a wrong total rather than only building new reports. A weak answer describes KEEPFILTERS as retaining filters without being able to produce the failing case, and the failing case is what earns the marks.

// Matrix rows = Dim_Product[Category]

// WITHOUT KEEPFILTERS: the category filter on the same column is replaced,
// so every row shows the same Beverages number
Wrong = CALCULATE ( [Total Sales], Dim_Product[Category] IN { "Beverages", "Snacks" } )

// WITH KEEPFILTERS: intersects, so rows outside the list correctly show blank
Right = CALCULATE ( [Total Sales], KEEPFILTERS ( Dim_Product[Category] IN { "Beverages", "Snacks" } ) )

// Layering conditions on one column
High Value in Band =
CALCULATE (
    [Total Sales],
    KEEPFILTERS ( Fact_Sales[Amount] > 5000 )
)

Answer

All of them require a marked Date table with a contiguous date column, and all of them are CALCULATE with a generated date filter underneath. TOTALYTD accumulates from the start of the year to the last date visible in the current context, and it takes an optional year end date argument, which in India you set to 31 March for a financial year to date. DATESYTD is the table returning equivalent you wrap in CALCULATE yourself.

SAMEPERIODLASTYEAR shifts the visible date range back exactly one year and is the standard prior year comparison. DATEADD is the general form, shifting by any number of days, months, quarters or years, and it is what you use for prior month or prior quarter. DATESINPERIOD returns a window of a given length ending at a given date, which is the right tool for rolling twelve months or rolling ninety days, because unlike DATEADD it handles partial periods and the start of history gracefully.

The measure interviewers most often ask you to write on the spot is year on year growth, and there are two things they are checking: that you compute the prior value with CALCULATE and a time intelligence function rather than by subtracting a hard coded year, and that you use DIVIDE and suppress the result when the prior period is blank, because a growth of infinity against a new product looks broken on a card. The Indian variant is worth naming out loud, since finance teams usually want financial year to date from April, and TOTALYTD with a year end date of 31 March gives it to you in one argument. A weak answer writes SAMEPERIODLASTYEAR and forgets that the denominator can be blank.

Total Sales = SUMX ( Fact_Sales, Fact_Sales[Quantity] * Fact_Sales[UnitPrice] )

// Indian financial year to date, April to March
Sales FYTD = TOTALYTD ( [Total Sales], 'Date'[Date], "31/03" )

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

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

// Rolling 12 months ending on the last visible date
Sales R12M =
CALCULATE (
    [Total Sales],
    DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH )
)

// The measure they ask you to write on the whiteboard
Sales YoY % =
VAR CurrentSales = [Total Sales]
VAR PriorSales   = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
    IF (
        NOT ISBLANK ( PriorSales ) && NOT ISBLANK ( CurrentSales ),
        DIVIDE ( CurrentSales - PriorSales, PriorSales )
    )

Answer

The first thing to say is that this is usually not a bug. A total row is not the sum of the rows; it is the same measure evaluated in a different, broader filter context. Once you say that, walk the causes.

Distinct counts do not add up, because a customer who bought in three months counts once in the total and once in each month, so the total is legitimately lower than the sum. Any measure containing an IF or a threshold behaves differently at the total, for example, a measure that flags customers spending over one lakh will evaluate the threshold once against the whole population at the total row. Ratios and averages never add, so an average of averages is wrong and a percentage column simply cannot sum.

Many to many relationships produce an unduplicated total that is lower than the rows. Time intelligence at a total can evaluate over the full visible range rather than per period. RANKX and other level sensitive functions produce nonsense at totals unless guarded.

Then give the fix, which is chosen per case: use HASONEVALUE or ISINSCOPE to blank the total when it is meaningless, or use SUMX over the grouping columns with VALUES or SUMMARIZE to force the total to be the sum of the rows when that is genuinely what the business wants. The communication half matters as much as the DAX half, because the person raising the ticket is usually a finance lead. A weak answer promises to fix the total without first working out whether the total is actually wrong.

// Case 1: distinct count. The total is correct and lower on purpose.
Active Customers = DISTINCTCOUNT ( Fact_Sales[CustomerKey] )

// Case 2: threshold measure. Force the total to sum the rows.
High Value Customers =
SUMX (
    VALUES ( Dim_Customer[CustomerKey] ),
    IF ( [Total Sales] > 100000, 1, 0 )
)

// Case 3: the total is meaningless at that level, so blank it out
Avg Days to Deliver =
IF (
    ISINSCOPE ( Dim_Store[Store] ),
    AVERAGEX ( Fact_Sales, DATEDIFF ( Fact_Sales[OrderDate], Fact_Sales[ShipDate], DAY ) ),
    BLANK ()
)

// Case 4: force row-wise summation across two grouping columns
Sum of Rows =
SUMX ( SUMMARIZE ( Fact_Sales, Dim_Store[Store], 'Date'[Month] ), [Some Measure] )

Answer

The rule of thumb every Power BI panel wants to hear is push it as far upstream as you can. If a view can be created in SQL Server, or a column can be added in the warehouse, do it there, because the work is done once for every consumer, it is testable, it is version controlled, and Power BI just reads the result. If that is not possible, which on client projects it very often is not because you have read only access, do it in Power Query, because transformations there run at refresh time, produce properly typed columns that VertiPaq compresses well, and keep the model clean.

Use DAX only for calculations that must respond to user selection, which means measures, and for the small number of columns that genuinely depend on the model rather than on the source. The reasoning to give is about cost and timing. Power Query work happens once per refresh; DAX calculated columns also happen once per refresh but compress worse and add dependency chains; DAX measures happen on every single visual interaction.

So moving work from measures to Power Query trades refresh time for query time, which is almost always the right trade for a report read by many people. The counterexample worth naming is when the calculation depends on what the user selected, which no upstream layer can know, and that is precisely what measures exist for. A weak answer says it depends on preference. There is a defensible default here, and interviewers want to hear it stated with the reason attached.

Answer

Query folding is Power Query translating your applied steps into a single native query that the source database executes, so the filtering, joining and grouping happen on the server and only the result travels to Power BI. When folding works, a query against a hundred million row SQL table can return a few thousand aggregated rows. When it breaks, Power Query pulls the whole table into memory on the gateway or your laptop and does the work locally, which turns a two minute refresh into a two hour one and is one of the most common real world causes of refresh timeouts.

You check it by right clicking the last applied step and looking at View Native Query. If it is greyed out, folding has stopped at or before that step, and you walk backwards step by step to find the last one where it is still enabled. Power BI Desktop also shows folding indicators on steps in recent versions, and for gateway refreshes you confirm with a trace on the source or with the query diagnostics pane.

The practical knowledge that separates candidates is knowing what breaks folding: adding an index column, most custom columns written with M functions that have no SQL equivalent, Table.Buffer, merging with a query from a different source type, changing types in certain orders, and anything after a step that has already broken it. Folding is also a hard prerequisite for incremental refresh, since the RangeStart and RangeEnd filters must reach the source. A weak answer defines folding correctly but cannot say how to verify it, and verification is the part the job actually needs.

// Folds: filter and group are translated into SQL and run on the server
let
    Source   = Sql.Database ( "bi-sql-prod", "SalesDW" ),
    Fact     = Source{[Schema="dbo", Item="FactSales"]}[Data],
    Filtered = Table.SelectRows ( Fact, each [OrderDate] >= #date(2024, 4, 1) ),
    Grouped  = Table.Group ( Filtered, {"StoreKey"}, {{"Amount", each List.Sum ( [Amount] ), type number }} )
in
    Grouped

// Breaks folding: Table.Buffer forces everything into memory locally
// Buffered = Table.Buffer ( Filtered ),

// Check: right click the step > View Native Query.
// Greyed out means folding already stopped at or before that step.

Answer

Append stacks tables vertically. It is what you use when the tables have the same shape and represent the same thing across different periods or regions, for example, twelve monthly sales extracts or four regional workbooks. Columns are matched by name, so a header typed differently in one file creates a new column full of nulls, which is the classic Append bug on Excel sources.

Merge joins tables horizontally on one or more key columns and produces a new column containing nested tables that you then expand to pick the fields you want. The join kinds are Left Outer, which is the default and the one you use most, Right Outer, Full Outer, Inner, and the two anti joins. The anti joins deserve a specific mention in an interview because they are the fastest way to answer data quality questions: Left Anti gives you the rows in the fact that have no matching dimension row, which is exactly your orphan key report, and Right Anti gives you dimension rows never used.

The practical points to raise are that merges on text keys need trimming and case normalisation first, that merging tables from two different sources breaks query folding and can be very slow, and that a merge on a non unique key silently multiplies rows, which is the Power Query version of the join fan out problem. Also worth saying: if you are merging a dimension into a fact just to get one attribute, consider leaving them as separate tables and building a relationship instead, because that is what the model is for. A weak answer describes merge as VLOOKUP and append as copy paste and stops.

Answer

This is the unpivot question and it is asked constantly because every Indian finance team sends exactly this file. A wide sheet with Apr, May, Jun as columns cannot be modelled, because Power BI needs one row per month per entity to relate it to a Date table. The fix is Unpivot Other Columns, selecting the identifier columns such as Region or Cost Centre and unpivoting everything else, which produces an Attribute column holding the month name and a Value column holding the number.

You then convert the month name to a real date, type the value column, and relate it to the Date table. The second half of the question, and the half people fail, is what happens when the next month is added. If you use Unpivot Columns and select the twelve month columns explicitly, the M code hard codes those names and next April's new column is silently ignored.

If you use Unpivot Other Columns and name only the identifier columns, any new month column is picked up automatically. That is the whole point of the distinction and it is the answer the interviewer is waiting for. Add to that the usual hygiene: promote headers, remove the blank spacer rows and the total row that finance always includes, filter out nulls created by empty cells, and use a folder source with Table.Combine if the file arrives one per month. A weak answer says unpivot and stops without knowing that Unpivot Other Columns is the future proof variant.

let
    Source    = Excel.Workbook ( File.Contents ( "D:\\Finance\\RegionSales.xlsx" ), null, true ),
    Sheet     = Source{[Item="Sales", Kind="Sheet"]}[Data],
    Promoted  = Table.PromoteHeaders ( Sheet, [PromoteAllScalars = true] ),
    NoTotals  = Table.SelectRows ( Promoted, each [Region] <> "Total" and [Region] <> null ),

    // Unpivot OTHER columns: any new month column is handled automatically
    Unpivoted = Table.UnpivotOtherColumns ( NoTotals, {"Region", "Cost Centre"}, "MonthName", "Amount" ),

    Typed     = Table.TransformColumnTypes ( Unpivoted, {{"Amount", type number}, {"MonthName", type text}} ),
    WithDate  = Table.AddColumn ( Typed, "MonthStart",
                    each Date.FromText ( "01 " & [MonthName] & " 2026" ), type date ),
    Cleaned   = Table.SelectRows ( WithDate, each [Amount] <> null )
in
    Cleaned

Answer

Incremental refresh means Power BI only reloads a recent window of data instead of the whole history, which is what makes a fact table with several years of data refreshable inside the service limits. The setup has three parts. First, create two date time parameters named exactly RangeStart and RangeEnd, since the service looks for those names.

Second, filter the fact query on its date column using those parameters, with the standard convention of greater than or equal to RangeStart and strictly less than RangeEnd so no row is counted twice at the boundary. Third, in the model, right click the table, choose Incremental refresh, and define the archive window and the refresh window, for example, store five years and refresh the last ten days. Power BI then partitions the table by period behind the scenes and only reprocesses the recent partitions.

The conditions that must hold are the part interviewers are really testing. The date column must be a date or date time and must be present in the source. The query must fold, because the RangeStart and RangeEnd filters have to be translated into a source predicate; if folding breaks, the source returns everything and you have gained nothing while adding complexity.

The source must support the filter being pushed down, which rules out most flat file sources. The parameters must not be used anywhere else in a way that breaks folding. You should also mention the two optional settings, detect data changes with a last modified column, and only refresh complete periods. A weak answer describes the dialog boxes and never mentions folding.

// Parameters (Manage Parameters): RangeStart and RangeEnd, type Date/Time

let
    Source   = Sql.Database ( "bi-sql-prod", "SalesDW" ),
    Fact     = Source{[Schema="dbo", Item="FactSales"]}[Data],
    Filtered = Table.SelectRows (
                   Fact,
                   each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd
               )
in
    Filtered

// Then: right click the table > Incremental refresh
//   Archive data starting 5 years before refresh date
//   Incrementally refresh data in the last 10 days
//   Optional: Detect data changes using [ModifiedDate]
//   Optional: Only refresh complete days
//
// Verify View Native Query still shows the date predicate in the WHERE clause.

Answer

A dataflow is Power Query running in the Power BI service rather than inside a PBIX file, storing its output in the service so that multiple models can consume the same prepared table. You would use one in four situations. First, reuse: when five reports all need the same cleaned customer dimension, a dataflow means the cleaning logic exists once instead of being copy pasted into five files that then drift apart.

Second, refresh isolation: a slow or fragile source can be refreshed on its own schedule into the dataflow, and the reports then load from the dataflow quickly, which also reduces load on the operational source system, something DBAs at client sites care about a lot. Third, governance: the transformation lives in a shared workspace where it can be owned by a central team rather than by whoever happens to hold the PBIX. Fourth, gateway consolidation, because the dataflow holds the gateway connection instead of every report author needing one.

The tradeoffs to name are that dataflows add a layer to debug, that refresh scheduling has to be sequenced so the report refreshes after the dataflow, and that the enhanced compute engine and linked entities need a Premium or Fabric capacity to be worth much. In 2026 you should also say that on Fabric the same job is increasingly done with Dataflow Gen2 writing into a Lakehouse or Warehouse, and that the choice between them is now part of the architecture conversation. A weak answer calls a dataflow online Power Query and cannot name a reason to prefer it.

Answer

A bookmark captures the current state of a page: which visuals are visible, what is selected in each slicer, the sort order, the drill level, and the spotlight state. The selection pane lists every object on the page and lets you show or hide each one, and it is also where you set the layer order and give objects sensible names. Together they are how you build interactivity without extra pages.

The four things worth naming as real uses are: a toggle that swaps a chart for its underlying table so users can see the numbers, a navigation menu made of buttons where each button jumps to a bookmark, a clear all filters button that restores a bookmark saved with default slicer values, and a hidden pane that slides in for filters so the canvas stays clean on a small laptop screen. The controls that matter are the bookmark options: Data captures slicer selections, Display captures visibility and formatting, Current page controls whether it navigates, and Selected visuals limits the bookmark to only the objects you had selected, which is essential for stopping one bookmark from resetting the whole page. Interviewers ask this because it separates people who have built a report somebody used from people who have only built charts. A weak answer describes bookmarks as saved views and cannot say how to stop a bookmark from also resetting the slicers, which is the Data checkbox and the Selected visuals option.

Answer

Drilldown moves within one visual down a hierarchy, so a column chart of category expands into subcategory and then into product, all in the same visual, using the drill controls in the header. It is the right choice when the levels are a natural hierarchy and the user wants the same measure at a finer grain. Drillthrough moves the user to a different page that has been configured with a drillthrough field, carrying the clicked value across as a filter, so right clicking a store in a summary takes them to a store detail page showing that store's trend, top products and open issues.

It is the right choice when the detail needs a different layout, not just a finer grain, and you can add a back button automatically. A tooltip page is a small report page set to tooltip page size and assigned to a visual, so hovering shows a mini report instead of a plain value; it is right when the extra context is glanceable and the user should not lose their place. The practical detail interviewers listen for is that drillthrough carries all filters applied to the source visual by default, and you can control that with the Keep all filters toggle, and that both drillthrough and tooltip pages should be hidden from the page tab strip so users do not land on them directly. A weak answer uses drilldown and drillthrough as synonyms, which is the most common mistake on this question.

Answer

Sync slicers let one slicer control visuals on several pages. You open View, Sync slicers, and then tick, per page, whether the slicer is synced and whether it is visible on that page. The common pattern is to sync the financial year slicer across every page but only show it on the first, so the selection persists as users navigate without the slicer eating canvas space everywhere.

The decision between the three filtering surfaces is a design judgement and that is what the panel is scoring. A slicer belongs on the canvas when the selection is central to the story and users will change it often, for example, month or region, and you should accept that it costs layout space and one extra query per slicer. The filter pane is right for the long tail of optional filters, because it is collapsible, it supports basic and advanced filter conditions that slicers cannot express, and it does not cost canvas space; you can lock filters so users cannot remove them and hide filters entirely from consumers.

A page level or report level filter belongs where a condition is structural rather than a user choice, for example, excluding cancelled orders or restricting a page to one business unit. The additional point worth making is performance: every visible slicer issues its own query on page load, so ten slicers on a page is a measurable slowdown before the user has done anything. A weak answer never mentions the filter pane at all.

Answer

Conditional formatting in Power BI offers colour scale, rules, and field value. Colour scale and rules are fine for simple thresholds, but they break down the moment the threshold depends on a target, a selection or a hierarchy level, because they are static settings stored on the visual. Field value is the answer: you write a measure that returns a colour, either a hex string or a named colour, and bind it under Conditional formatting, Format style, Field value.

Because it is a measure, it is evaluated in the filter context of every individual cell, so it can compare actual against target per row, respect the slicer selection, and return different colours at different matrix levels. The same technique drives data bars, icons, the background colour of a card, and the title colour. Two practical points earn credit.

First, accessibility, because red and green alone are not distinguishable for a meaningful share of users, so pair colour with an arrow icon or a plus and minus sign. Second, maintainability, because putting hex codes inside a DAX measure means a brand colour change is a code change; a small disconnected colour table or a shared set of colour measures keeps that manageable. The interviewer is checking whether you know Power BI formatting can be data driven at all, since a surprising number of candidates only ever use the rules dialog. A weak answer explains the rules dialog and stops.

Variance vs Target = [Total Sales] - [Target Sales]

// Returns a colour, bound via Conditional formatting > Format style > Field value
Variance Colour =
VAR Pct = DIVIDE ( [Variance vs Target], [Target Sales] )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( Pct ),  "#BFBFBF",
        Pct >=  0.05,     "#1E7B45",
        Pct >= -0.05,     "#B58A00",
        "#B3261E"
    )

// Pair colour with a symbol so the meaning survives without colour vision
Variance Label =
VAR Pct = DIVIDE ( [Variance vs Target], [Target Sales] )
RETURN
    IF ( Pct >= 0, "+", "" ) & FORMAT ( Pct, "0.0%" )

Answer

Accessibility work in Power BI is concrete, not vague. You set tab order and alt text for every visual in the Selection and Accessibility panes, so screen reader users get a sensible reading sequence rather than the creation order. You check colour contrast and never encode meaning in colour alone.

You use the built in themes that are designed for colour vision deficiency, or supply your own with sufficient contrast. You give visuals real titles rather than the auto generated Sum of Amount by Month. You make sure everything reachable by mouse is reachable by keyboard, and you know that Power BI has a Show data table shortcut so a user can read the numbers behind a chart.

Mobile is a separate layout, not a resize: you open View, Mobile layout, and drag only the visuals that matter onto a phone canvas, usually a small number of cards and one or two simple charts, dropping dense matrices entirely. If you do not build a mobile layout the report still opens on a phone, just as a shrunken desktop page that nobody can read. Interviewers care for two reasons.

First, many Indian clients in banking, government and pharma have accessibility clauses in the contract, so this is a delivery requirement, not a nicety. Second, senior stakeholders read reports on their phone, so a report with no mobile layout gets a reputation for being unusable regardless of how good the model is. A weak answer says use good colours and stops.

Answer

Both exist to stop the combinatorial explosion of measures and visuals. Field parameters let a user switch which field a visual uses, so one chart can be sliced by region, category, channel or salesperson depending on a slicer, instead of four charts hidden behind bookmarks. You create them from Modeling, New parameter, Fields, which generates a small disconnected table of field references and a helper measure, and you can include either dimension columns or measures.

Calculation groups solve the other axis: instead of writing Sales YTD, Sales LY, Sales YoY, Margin YTD, Margin LY and Margin YoY as six separate measures, you write one calculation group with calculation items YTD, LY and YoY that each wrap SELECTEDMEASURE, and every measure in the model gains all three variants. They are created in Tabular Editor rather than in Power BI Desktop's main interface for most versions, which is a detail interviewers use to check whether you have really used them. The differences to state clearly: field parameters change what is on the axis, calculation groups change how a measure is evaluated; field parameters live in the report layer and are easy for report authors, calculation groups live in the model layer and need external tooling plus care with formatting strings and precedence when more than one group exists.

Both drastically cut maintenance. A weak answer treats them as the same feature or has only heard of one of them.

// Field parameter generated by Modeling > New parameter > Fields
Slice By = {
    ( "Region",   NAMEOF ( 'Dim_Store'[Region] ),      0 ),
    ( "Category", NAMEOF ( 'Dim_Product'[Category] ),  1 ),
    ( "Channel",  NAMEOF ( 'Dim_Channel'[Channel] ),   2 )
}

// Calculation group items (authored in Tabular Editor)
// Item: Current
SELECTEDMEASURE ()

// Item: YTD
CALCULATE ( SELECTEDMEASURE (), DATESYTD ( 'Date'[Date], "31/03" ) )

// Item: LY
CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

// Item: YoY %
VAR Cur   = SELECTEDMEASURE ()
VAR Prior = CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN DIVIDE ( Cur - Prior, Prior )

Answer

VertiPaq is a columnar in memory engine. It stores each column separately, builds a dictionary of the distinct values in that column, replaces the values with integer indexes, and then applies run length encoding and other compression on top. The consequence that drives every optimisation decision is that compression quality depends almost entirely on cardinality, the number of distinct values in a column, and hardly at all on the number of rows.

A hundred million row table with a status column holding four values compresses to almost nothing. A one million row table with a full date and time stamp accurate to the second holds close to a million distinct values, so the dictionary is huge, run length encoding finds nothing to collapse, and that single column can dominate the whole model. Splitting it into a date column and a time column rounded to the minute or the hour collapses cardinality from about a million to a few thousand, and it is entirely normal for that one change to cut model size by a third or more, which is why the question is asked in this shape.

The other levers follow from the same principle: remove columns nobody uses, especially free text notes and GUID keys, replace long text keys with integer surrogate keys, avoid high precision decimals where fixed decimal is enough, and do not import audit columns just because they exist. Sort order also matters, since data sorted by a low cardinality column compresses better. A weak answer says Power BI compresses data well and cannot connect cardinality to size.

// The single highest impact change on most models, done in Power Query:
//   OrderDateTime  (2026-08-17 14:23:07)   ~1,000,000 distinct values
// becomes
//   OrderDate      (2026-08-17)            ~1,800 distinct values
//   OrderHour      (14)                    24 distinct values

// Power Query M
// AddDate = Table.AddColumn ( Source, "OrderDate", each Date.From ( [OrderDateTime] ), type date ),
// AddHour = Table.AddColumn ( AddDate, "OrderHour", each Time.Hour ( [OrderDateTime] ), Int64.Type ),
// Dropped = Table.RemoveColumns ( AddHour, {"OrderDateTime"} )

// Check the damage first with Vertipaq Analyzer in DAX Studio:
//   Columns tab, sort by Total Size, look at Cardinality next to it.

Answer

Start inside Power BI Desktop with Performance Analyzer. You open the pane, click Start recording, refresh the visuals, and it gives you a per visual breakdown split into DAX query time, visual display time and other. That split is the whole point, because it tells you immediately whether the problem is your measure, the visual itself, or something else such as too many objects on the page.

A visual taking four seconds with three seconds in DAX is a measure problem; a visual taking four seconds with three seconds in display is usually a matrix returning tens of thousands of rows or a custom visual behaving badly. Performance Analyzer also lets you copy the generated DAX query, which is the handoff to the second tool. DAX Studio connects to the open model, runs that query with Server Timings enabled, and shows you how much time went to the storage engine versus the formula engine, how many storage engine queries were issued, and whether results were served from cache.

It also hosts Vertipaq Analyzer, which reports table and column sizes, cardinality, and how much space dictionaries and hierarchies take, so you can find the column that is bloating the model. Tabular Editor adds Best Practice Analyzer, which flags modelling problems in bulk. For the service, the workspace usage metrics report and, on capacity, the Fabric Capacity Metrics app show you slow refreshes and throttling.

The order matters as much as the list: measure first, then read the query plan, then look at the model. A weak answer starts optimising DAX without measuring anything.

Answer

A DAX query is executed by two components. The storage engine, VertiPaq for imported models, is multi threaded, works on compressed columns, caches its results, and is very fast, but it only understands a restricted set of operations, essentially scans, filters and simple aggregations. The formula engine is single threaded, understands the whole DAX language, and does everything the storage engine cannot, including complex row by row logic and joins that are not simple relationship traversals.

It does not cache results. So the tuning goal is to push as much work as possible into the storage engine and keep the formula engine's job small. In DAX Studio's Server Timings you see total duration, storage engine time, formula engine time and the number of storage engine queries.

Three patterns tell you what is wrong. High formula engine time with few storage engine queries means the logic is too complex for the storage engine to handle, often because of nested iterators or context transition over a large table. A very large number of small storage engine queries usually means a CallbackDataID, which is the storage engine stopping to ask the formula engine to evaluate something in the middle of a scan; that appears when you put IF logic or a measure reference inside an iterator over a fact table.

A single enormous storage engine scan means you are materialising too many rows, usually from FILTER over the fact table. The fixes follow: replace fact table FILTER with dimension predicates, move row level logic into a Power Query column, and reduce context transition. A weak answer cannot name the two engines at all, which is fine at junior level and disqualifying at senior level.

Answer

Import loads a compressed copy of the data into the model. It is the fastest to query, supports the full DAX surface, and works offline from the source, but the data is only as fresh as the last refresh and the model must fit within the size limits of your licence. DirectQuery leaves the data at the source and sends a query on every interaction, so data is current and there is no size limit, but every visual costs a round trip, some DAX functions are unavailable or discouraged, and report performance is now the source database's problem, which is a real risk on a shared operational server.

Dual is a storage mode you set per table, usually on dimensions, which lets the engine use the imported copy when a query can be answered from cache and switch to DirectQuery when it is joined to a DirectQuery fact, avoiding slow single table round trips for slicers. Live Connection is different in kind: you are connecting to an existing semantic model in the Power BI service or to Analysis Services, so there is no model of your own, you cannot add tables, and until recently you could not add your own measures without converting to a composite model. A composite model mixes storage modes in one model, and since composite models on Power BI semantic models arrived you can extend somebody else's published model with your own tables and measures.

The tradeoffs an interviewer expects you to name are freshness against speed, model size against source load, and governance, because a composite model on a certified dataset can quietly diverge from the certified definitions. A weak answer only says Import is faster.

Answer

A workspace is the container where reports, semantic models, dashboards and dataflows live and where development happens. It has four roles: Admin, who can manage access and delete the workspace, Member, who can publish and share, Contributor, who can publish and edit content but not manage access, and Viewer, who can only read. The mistake candidates make is proposing to add two hundred consumers as Viewers to the development workspace, which is wrong for two reasons: they then see work in progress, and any change you save is instantly live to everyone.

The correct pattern is to publish an app from the workspace. An app is a curated, packaged, read only presentation of selected reports from that workspace, with its own audience list, its own navigation, and, crucially, its own publish action, so you control when consumers see changes. You give the app to a security group, never to two hundred individual email addresses, because group membership is managed by IT and survives people leaving.

You should also mention that a Viewer needs a Pro licence unless the workspace sits on a Premium or Fabric capacity, which is where licensing enters the design decision. The mature version of the answer adds that you would keep development, test and production workspaces separate and move content between them with a deployment pipeline. A weak answer says share the report link, which does not scale, gives no versioning control, and creates a permissions mess nobody can audit later.

Answer

A gateway is software installed on a machine inside your network that lets the Power BI service reach data that is not publicly accessible, which in practice means on premises SQL Server, SAP, Oracle, file shares and Excel files on a network drive. You need one for scheduled refresh of any import model whose source is on premises, and for every query of a DirectQuery or Live Connection model against such a source. You do not need one for cloud sources such as Azure SQL, SharePoint Online or a web API, though organisations sometimes still route them through one for network policy reasons.

Personal mode is tied to one user, runs under that person's credentials, supports import refresh only, and cannot be shared. It is fine for an individual analyst prototyping and is a serious risk in production, because the refresh stops the day that person leaves or changes their password, and I would name that risk explicitly in an interview. Standard mode, previously called enterprise mode, is installed as a Windows service on a server, is administered centrally, supports multiple users and multiple data sources with credentials stored per source, supports DirectQuery and Live Connection as well as import, and can be clustered across several machines for high availability and load balancing.

The operational detail worth adding is that the gateway machine needs to stay on, needs enough memory because merges and non folding steps execute there, and needs its monthly update applied, since Microsoft supports only recent versions. A weak answer knows what a gateway is but not that personal mode cannot serve DirectQuery.

Answer

Scheduled refresh is configured on the semantic model in the service: you supply credentials for each data source, pick a time zone, and add refresh times. On a Pro workspace you are typically allowed up to eight scheduled refreshes a day, and on Premium Per User or a capacity backed workspace that rises to around forty eight, with the exact numbers set by Microsoft and worth confirming against current documentation before you quote them in an interview. Beyond the schedule you can trigger refreshes through the REST API, through Power Automate, or through a Fabric pipeline, which is how teams that need hourly or event driven updates work around the schedule limit.

Licensing in 2026 has three practical tiers. Pro is a per user licence needed both to publish and, unless the content sits on a capacity, to consume. Premium Per User is a per user licence that unlocks the larger model sizes, more frequent refreshes, deployment pipelines, XMLA endpoint write access and paginated reports, but everyone who views the content also needs Premium Per User, which is why it suits small analytics teams rather than wide distribution.

Fabric capacity, the F stock keeping units, is a purchased capacity where content in capacity backed workspaces can be consumed by users with a free licence, which is what makes wide internal distribution affordable. The design consequence to state is that the licensing tier decides your refresh frequency, your model size ceiling and whether consumers need paid licences, so it belongs in the architecture conversation, not at the end of the project.

Answer

There are two designs. Static row level security means you create a role per group in Power BI Desktop under Modeling, Manage roles, and give each role a DAX filter such as region equals West, then assign users to that role in the service. It is simple and it is acceptable when there are three or four fixed groups, but it does not scale, because adding a region means editing and republishing the model.

Dynamic row level security means you create a single role whose filter resolves the current user at query time using USERPRINCIPALNAME, which returns the signed in user's email in the service, matched against a security table that maps email to region. Now adding a manager is a data change, not a model change, which is the whole point. For hierarchies where a manager should see their whole reporting tree, you extend this with PATH and PATHCONTAINS on a parent child table.

Testing has two levels and interviewers always ask about both. In Desktop, use Modeling, View as, pick the role, and optionally supply a user name to test dynamic rules, which simulates the filter and lets you confirm both that the right rows appear and that totals shrink accordingly. In the service, open the workspace, find the semantic model, choose Security, add users or security groups to the role, and use Test as role from the same menu.

The traps to name are that row level security does not apply to workspace Admins, Members or Contributors, so it must be tested with a Viewer account, that USERNAME behaves differently in Desktop and in the service, and that bidirectional relationships can leak rows unless you enable security filtering on the relationship. A weak answer writes the DAX and never mentions testing or the admin bypass.

// Security table imported from HR: Dim_UserSecurity[Email], [Region]

// Static role: one role per region, filter on Dim_Store
[Region] = "West"

// Dynamic role: ONE role for everybody, filter on Dim_Store
[Region] =
LOOKUPVALUE (
    Dim_UserSecurity[Region],
    Dim_UserSecurity[Email], USERPRINCIPALNAME ()
)

// Or, filtering the security table itself and letting the relationship propagate
[Email] = USERPRINCIPALNAME ()

// Manager hierarchy: sees self plus everyone below
PATHCONTAINS (
    LOOKUPVALUE ( Dim_Employee[Path], Dim_Employee[Email], USERPRINCIPALNAME () ),
    Dim_Employee[EmployeeKey]
)

// Test: Desktop > Modeling > View as > Other user + Role
//       Service > Semantic model > Security > Test as role

Answer

A deployment pipeline is the Power BI service feature that links three workspaces, Development, Test and Production, and lets you promote content between them with a compare and deploy step rather than by republishing from Desktop. It shows you what differs between stages, lets you deploy selected items, and keeps the lineage, so consumers of the production app are not disturbed while you work in development. The part the question is really asking about is deployment rules.

Because the development model points at the development database and the production model must point at the production database, you set data source rules and parameter rules on the target stage, so that when content is deployed the connection string or the parameter value is swapped automatically. The professional way to build for this is to put the server name and database name in Power Query parameters from day one, then the rule is a parameter rule and you never edit an M expression during a deployment. Points that add credibility: pipelines need Premium Per User or a Fabric capacity, so on a Pro only tenant you fall back to separate workspaces plus parameters plus manual republish or the REST API; deployment rules are set on the target stage and only apply to content deployed into it; and for real source control you would use Fabric git integration to store the model definition in a repository so changes are reviewable. A weak answer describes publishing from Desktop to a production workspace, which is exactly the practice a pipeline question is designed to catch.

Answer

Start by separating the report layer, the model layer and the source layer, and by getting one fact before you touch anything: is it slow for everyone or for one person, on first open or every time, and did it change recently. If it changed recently, ask what was deployed. Then run Performance Analyzer on the page and read the per visual split.

If one visual dominates and its time is mostly DAX, copy the query into DAX Studio, run it with Server Timings, and check the storage engine versus formula engine split, which tells you whether you have a materialisation problem or a logic problem. If time is spread thinly across twenty visuals, the problem is page design, not DAX, and the fix is fewer visuals, fewer slicers, and a summary page that drills through to detail. If display time dominates on one visual, look for a matrix returning tens of thousands of rows or a custom visual.

Then go to the model with Vertipaq Analyzer: check total model size, find the largest columns, and look for high cardinality columns nobody uses. Typical wins in order of frequency are removing unused high cardinality columns, splitting a datetime, replacing fact table FILTER with dimension predicates, moving row level logic into Power Query, adding aggregation tables for a DirectQuery model, and cutting the number of visuals per page. Finish by saying you would measure again after each change and record the before and after, because a triage story without numbers is not evidence. A weak answer starts rewriting DAX before measuring anything.

Answer

Budget the time out loud before you start, because the evaluator is grading process as much as output. Roughly ten minutes to profile and clean, fifteen to model, twenty to write measures, twenty five to build two pages, and the last ten to polish and prepare what you will say. In profiling, use Column quality, Column distribution and Column profile in Power Query, switch profiling to the whole dataset rather than the first thousand rows, and check the grain by comparing row count with the distinct count of the candidate key.

In cleaning, set types explicitly, trim and clean text keys, remove the columns you will not use, and rename everything to business language. In modelling, split the flat file into a small star: a fact plus a Date table plus two or three dimensions created with Table.Distinct, then mark the Date table. In measures, write a base measure and then build on it, use VAR and RETURN, and include at least one time intelligence measure and one percentage of total, since those two prove you understand context.

In the report, build a summary page with cards, a trend, a ranked bar and a slicer, and a detail page reachable by drillthrough. In the last ten minutes add a dynamic title, format numbers in lakhs or crores if the audience is Indian finance, and write down the three assumptions you made. Then present: what the data says, what you assumed, what you would do with another day. A weak submission is six visuals on one page with no model and no stated assumptions.

TIME BUDGET FOR A 90 MINUTE BUILD

0:00 to 0:10   Profile: column quality, distribution, grain check, date coverage
0:10 to 0:25   Clean and model: types, trim keys, split into a star, Date table, mark it
0:25 to 0:45   Measures: base, time intelligence, percentage of total, one ratio
0:45 to 1:10   Two pages: summary with cards and trend, detail via drillthrough
1:10 to 1:20   Polish: dynamic title, number formatting, tidy names, tooltips
1:20 to 1:30   Write down assumptions and rehearse a two minute walkthrough

WHAT TO SAY AT THE END
- Here is the grain I found and how I verified it
- Here are the three assumptions I made and why
- Here is what I would add with another day: RLS, incremental refresh, a target table

Answer

This is a judgement question and the wrong answer is enthusiasm. Power BI wins on three things. Cost and integration, because it is bundled into the Microsoft ecosystem most Indian enterprises already run, with Entra identity, Teams embedding, Excel connectivity to the semantic model and now Fabric alongside it.

Modelling depth, because DAX and VertiPaq give you a reusable semantic layer that many reports share, which is a stronger centre of gravity than a per workbook approach. And the refresh, security and distribution story out of the box. Tableau wins on visual analysis flow, on the polish of exploratory work, and it is genuinely strong in organisations where analysts, not central BI teams, drive the work; ripping out a working Tableau estate to save licence cost is a project with a poor return, and saying that out loud shows maturity.

Excel is not the enemy and never wins by being replaced. The honest position is that Excel remains the right tool for ad hoc analysis, for what if modelling, and for finance workflows where the user must type into cells; the best answer is usually Power BI as the governed semantic model with Excel connected to it through Analyze in Excel, so finance keeps their pivot tables while the numbers come from one definition. When would you say no to Power BI?

When the organisation is fully on a non Microsoft identity and data stack, when the requirement is a highly bespoke embedded analytics experience where a code first library fits better, or when there is no appetite to own a data model at all and the real need is one recurring extract. A weak answer just lists features.