Day 10: DAX — Advanced Patterns

3-Month Moving Average = CALCULATE( AVERAGE(Sales[Revenue]), DATESINPERIOD( 'Date Table'[Date], LASTDATE('Date Table'[Date]), -3, MONTH ) ) DATESINPERIOD returns a set of dates starting from LASTDATE (the last date in context) going back 3 months. CALCULATE then evaluates AVERAGE within that date range. Use in a line chart with Month on the axis to smooth out seasonal volatility.

RLS restricts which rows of data a user sees based on their identity. Implementation: In Power BI Desktop, go to Modelling → Manage Roles → New Role → define a DAX filter expression on a table (e.g. [Region] = USERPRINCIPALNAME() or [RegionManager] = USERNAME()). Publish to Power BI Service → dataset settings → Security → add users to each role. When those users view the report, they only see rows matching their RLS filter. Test with "View as" in Desktop before publishing.

ALLEXCEPT(table, column1, column2, ...) removes all filters from a table EXCEPT the specified columns. Use when you want to remove most filters but keep one or two. Example: calculating revenue as % of region total (keeping Region filter but removing Product filter): Revenue % of Region = DIVIDE( SUM(Sales[Revenue]), CALCULATE(SUM(Sales[Revenue]), ALLEXCEPT(Sales, Sales[Region])) ) This keeps the Region filter intact while removing all other filters (Product, Month, etc.) for the denominator.

What-If Parameters create a user-controlled numeric slicer that feeds into DAX measures, enabling scenario analysis. In Power BI Desktop: Modelling → New Parameter → set Name, Data Type, Min, Max, Increment, Default. Power BI creates a disconnected table with the parameter values and a measure to retrieve the selected value. Use in a measure: Adjusted Revenue = SUM(Sales[Revenue]) * (1 + [Growth Rate Value]). A user moves the Growth Rate slicer from 0% to 20% and the report dynamically shows projected revenue — without touching the underlying data.

EARLIER(column) was the original way to reference the outer row context when inside a nested row context (e.g. in FILTER inside a calculated column). It is complex to understand and error-prone. VAR (variable) is the modern replacement — it captures a value before evaluation and uses it inside complex expressions, making DAX much more readable. Example with VAR: Rank = VAR CurrentRevenue = SUM(Sales[Revenue]) RETURN RANKX(ALL(Products), SUM(Sales[Revenue]), CurrentRevenue, DESC) Always use VAR/RETURN instead of EARLIER — it is clearer, easier to debug, and Microsoft recommends it.