Power BI DAX Masterclass

DAX (Data Analysis Expressions) is the formula language that powers Power BI. Mastering it means understanding not just the syntax, but the evaluation context model that underpins every calculation. This article covers the patterns and functions that matter most in real-world reporting work.

Calculated Column vs. Measure

In Power BI, measures and calculated columns serve distinct purposes.

Calculated Columns

A calculated column creates a new physical column in your data table. It calculates values row by row based on a formula and physically exists in the table. Use calculated columns when you need persistent data that doesn't depend on report context — for example, data transformations or category labels.

Measures

A measure does not physically exist in the table. It is calculated dynamically when needed, typically when you visualize data in reports. Measures are evaluated in the context of the filters and slicers applied in your reports, allowing for flexible and complex calculations. They are highly reusable across different visuals and reports.

Key differences:

• Calculated columns operate at the row level and are static until refreshed; measures are dynamic and depend on the visual filtering context.
• Calculated columns with complex calculations over large datasets can significantly affect model performance.

Generally, measures are preferred for their flexibility and dynamic nature.

Date Tables

Power BI provides two functions for creating date tables: CALENDARAUTO and CALENDAR.

CALENDARAUTO

Automatically generates a date table based on the existing date values in your model. Finds the earliest and latest dates in your data to create a contiguous range. No customisation of the start date or fiscal year settings.

CALENDAR

Requires you to manually specify the start and end dates. Provides more flexibility for exact ranges and fiscal year adjustments.

Date Table Script

Based on an existing sales table and date column:

DateTable = 
ADDCOLUMNS ( 
  CALENDAR(MINX('sales','sales'[date]), MAXX('sales','sales'[date])),
  "DateAsInteger", FORMAT ( [date], "YYYYMMDD" ),
  "Year", YEAR ( [date] ),
  "MonthNo", FORMAT ( [date], "MM" ),
  "YearMonthNo", FORMAT ( [date], "YYYY/MM" ),
  "YearMonth", FORMAT ( [date], "YYYY/mmm" ),
  "MonthShort", FORMAT ( [date], "mmm" ),
  "MonthLong", FORMAT ( [date], "mmmm" ),
  "WeekNo", WEEKDAY ( [date] ),
  "WeekDay", FORMAT ( [date], "dddd" ),
  "WeekDayShort", FORMAT ( [date], "ddd" ),
  "Quarter", "Q" & FORMAT ( [date], "Q" ),
  "YearQuarter", FORMAT ( [date], "YYYY" ) & "/Q" & FORMAT ( [date], "Q" )
)

Key Measures Table

A Key Measures table consolidates all your measures in one place, making them easier to manage across reports.

Creating the table:

1. Go to Home → Enter Data in Power BI
2. Name the table "Key Measures Table" — no data needed
3. Click Load to create the empty table
4. Move existing measures into this table via the measure's Home Table property

COUNT Aggregation Functions

X (Iterator) Functions

Iterator functions process data row by row, then aggregate the results. They are essential for calculations that depend on row context.

• SUMX — iterates a table, evaluates an expression per row, returns the sum
• AVERAGEX — same iteration, returns the average
• MINX / MAXX — returns the smallest or largest value from the row-by-row evaluation
• COUNTX — counts non-blank results from the expression per row
• MEDIANX — returns the median

Filter vs. Row Context

Filter Context

Refers to the set of filters applied to data before performing calculations. Determines which rows are included. Modified by CALCULATE:

TotalSalesElectronics = CALCULATE(
  SUM(Sales[SalesAmount]),
  FILTER(Sales, Sales[ProductCategory] = "Electronics")
)

Row Context

Exists when a calculation is performed on a per-row basis. Iterator functions like SUMX create row context:

TotalRevenue = SUMX(Sales, Sales[Quantity] * Sales[Price])

Context transition: Using CALCULATE inside a row context converts it to a filter context.

CALCULATE Function

CALCULATE modifies the filter context before performing a calculation.

CALCULATE(<expression>, <filter1>, <filter2>, ...)

Example:

TotalSalesElectronics = CALCULATE(
  SUM(Sales[SalesAmount]),
  FILTER(Sales, Sales[ProductCategory] = "Electronics")
)

CALCULATE can apply multiple filters and is the cornerstone of context-aware DAX measures.

FILTER Function

Returns a table that includes only the rows meeting specific criteria. Used as a table argument inside CALCULATE:

FILTER(<table>, <filter_expression>)

Example:

FilteredProducts = FILTER(Products, Products[Price] > 5)

FILTER is a table function — it returns a table, not a scalar value.

ALL, ALLSELECTED, ALLEXCEPT

ALL

Removes all filters from a table or column. Use to calculate values against the complete dataset:

TotalSalesAll = CALCULATE(SUM(Sales[SalesAmount]), ALL(Sales))

Percentage of total:

Revenue % by State = [Revenue Measure] / CALCULATE([Revenue Measure], ALL(location[state]))

ALLSELECTED

Removes filters but respects selections made by slicers in the report:

SalesPercentage = DIVIDE(
  SUM(Sales[SalesAmount]),
  CALCULATE(SUM(Sales[SalesAmount]), ALLSELECTED(Sales))
)

ALLEXCEPT

Removes filters from all columns in a table except the specified ones:

TotalSalesByRegion = CALCULATE(
  SUM(Sales[SalesAmount]),
  ALLEXCEPT(Sales, Sales[Region])
)

Logical Operators

IF(Sales[Amount] > 1000 && Sales[Region] = "North", "High Sale", "Low Sale")

VALUES and AVERAGEX

VALUES returns a one-column table of distinct values. Combined with AVERAGEX, you can compute averages over distinct groups:

MonthlyAverageRevenue = AVERAGEX(VALUES(DateTable[YearMonth]), [RevenueMeasure])

RANKX

Ranks values within a specified context:

Ranking by Quarterly Average = CALCULATE(
  RANKX(ALLSELECTED(customer), [Quarterly Average Revenue]),
  ALL(DateTable[Year])
)

• ALLSELECTED(customer) — ranks customers respecting the current slicer selection
• ALL(DateTable[Year]) — removes year filters so ranking is consistent across the time axis

Top-N Filter with IF

Combine RANKX with a helper table and IF to show only top-N entries:

TopN Revenue = IF(
  [Ranking by Quarterly Average] <= MAX(TopNFilter[TopNValue]),
  [Revenue Measure],
  BLANK()
)

Variables

Variables improve readability and performance by storing intermediate results:

TotalSalesExcludingCategory = 
VAR TotalSales = SUM(Sales[SalesAmount])
VAR ExcludedSales = CALCULATE(SUM(Sales[SalesAmount]), Sales[Category] = "ExcludedCategory")
RETURN
    TotalSales - ExcludedSales

Variables are evaluated once and referenced multiple times, avoiding redundant calculations.

Time Intelligence: DATEADD

Shifts dates by a specified number of intervals for period-over-period comparisons:

DATEADD(<dates>, <number>, <time_unit>)

Example — revenue two days ago:

RevenueTwoDaysAgo = CALCULATE(
  [RevenueMeasure],
  DATEADD(DateTable[Date], -2, DAY)
)

Supported intervals: DAY, MONTH, QUARTER, YEAR. Use negative numbers to look back.