Day 25: Power BI — Technical Interview Q&A Set 2

Context transition occurs when a row context (the current row being iterated) is converted to an equivalent filter context. This happens automatically when a measure is referenced inside a calculated column or an iterator function (SUMX, AVERAGEX, etc.). Example: in a SUMX iteration, calling [Total Revenue] inside the iterator triggers context transition — the current row's values become filter context for the measure. This is why measures inside iterators can give different results than expected if you do not account for the transition. Context transition is one of the most complex DAX concepts and a common source of subtle calculation bugs.

ALL removes all filters from a table or column — including slicer selections. The result is always the absolute grand total regardless of any user interaction. ALLSELECTED removes filters applied by cross-filtering from other visuals but keeps filters from slicers and page/report-level filters. Use ALLSELECTED for "% of total as the user sees it": Revenue % of Visual Total = DIVIDE(SUM(Sales[Revenue]), CALCULATE(SUM(Sales[Revenue]), ALLSELECTED(Sales[Product]))). If the user filters to "North" via a slicer, ALLSELECTED keeps North — so the denominator is North's total, not the grand total. ALL ignores the slicer and uses the absolute grand total.

TREATAS(table_expression, column1, column2, ...) applies a virtual relationship — it treats the values in a table expression as if they were filter values for the specified columns. Used when you need to filter a table using values from an unrelated table (no physical relationship). Example: applying a dynamic filter set from a What-If parameter table to the Sales table without creating a relationship: CALCULATE(SUM(Sales[Revenue]), TREATAS(SelectedProducts, Sales[ProductID])). TREATAS is an advanced alternative to USERELATIONSHIP when no physical relationship exists.

Create a disconnected measures table: Measure Name | Measure Index (Revenue, 1), (Profit, 2), (Orders, 3). Add a slicer for Measure Name. Create a DAX measure: Selected Measure = SWITCH(SELECTEDVALUE(MeasureTable[Measure Index]), 1, SUM(Sales[Revenue]), 2, SUM(Sales[Profit]), 3, COUNTROWS(Sales)). Use Selected Measure in all visuals. When the user selects "Revenue" in the slicer, all visuals show revenue. When they select "Profit", all visuals switch to profit — without creating multiple versions of the report.

Incremental Refresh only refreshes new or changed data rather than re-loading the entire dataset on each refresh. For a dataset with 3 years of sales history, only the last 3 days of new data needs to refresh — the 3-year history stays cached. Setup: in Power Query, create RangeStart and RangeEnd parameters (exact names required) → filter the date column to between these parameters → in Power BI Desktop, right-click the table → Incremental Refresh → define the historical and refresh windows. Benefits: much faster refresh, less source database load, enables larger datasets within Pro limits. Requires Power BI Premium for datasets larger than 1 GB.