DAX Fundamentals and Measures

Quick Answer: DAX measures are dynamic formulas that calculate on-the-fly based on the user's filter selections (filter context). Unlike calculated columns, measures are NOT stored in the model — they compute at query time. Always create explicit measures instead of relying on implicit auto-aggregations, and use VAR/RETURN for readable, efficient formulas.

What is DAX?

DAX (Data Analysis Expressions) is the formula language used in Power BI for:

• Measures — Dynamic calculations evaluated at query time
• Calculated columns — Row-by-row computations stored in the model
• Calculated tables — Entire tables generated from expressions
• Row-level security — Rules that filter data by user identity

Measures vs. Calculated Columns

Creating Measures

Basic Syntax

Measure Name = DAX_EXPRESSION

Simple Aggregation Measures

Total Revenue = SUM(Sales[Amount])
Total Quantity = SUM(Sales[Quantity])
Order Count = COUNTROWS(Sales)
Average Order Value = AVERAGE(Sales[Amount])
Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

Implicit vs. Explicit Measures

Implicit measures are auto-aggregations Power BI applies when you drag a numeric column into a visual:

• Dragging Amount shows "Sum of Amount" by default
• The aggregation can be changed per visual (Sum, Average, Count, etc.)

Explicit measures are DAX formulas you write:

Total Revenue = SUM(Sales[Amount])

Why explicit measures are better:

1. Consistent behavior across all visuals
2. Reusable in other measures and visuals
3. Formatted consistently (currency, percentage)
4. Appear in a single location in the field list
5. Required for complex calculations (ratios, time intelligence)

Exam Tip: The PL-300 expects you to know why explicit measures should replace implicit measures. Always create explicit measures for any value that appears in reports.

Understanding Filter Context

Filter context is the set of active filters that determine which data a measure evaluates. Filters come from:

• Visual filters — the chart's axes, legend, and filter pane
• Slicers — interactive filter controls on the report page
• Cross-filtering — selections in other visuals
• Report/page-level filters — filters applied to the entire report or page
• Row-level security — filters applied based on user identity

Example:

Total Revenue = SUM(Sales[Amount])

Understanding Row Context

Row context exists when DAX iterates over table rows:

• Calculated columns — automatically evaluate per row
• Iterator functions — SUMX, AVERAGEX, COUNTX, MAXX, MINX create row context

// Calculated column (row context — evaluates per row)
Line Total = Sales[Quantity] * Sales[UnitPrice]

// Iterator measure (creates row context inside a measure)
Total Revenue =
SUMX(Sales, Sales[Quantity] * Sales[UnitPrice])

Variables (VAR/RETURN)

Variables store intermediate results for reuse within a DAX expression:

Profit Margin =
VAR TotalRevenue = SUM(Sales[Revenue])
VAR TotalCost = SUM(Sales[Cost])
VAR Profit = TotalRevenue - TotalCost
RETURN
    IF(TotalRevenue = 0, BLANK(),
        DIVIDE(Profit, TotalRevenue))

Variable Benefits

1. Readability — Named intermediate values are easier to understand
2. Performance — Values computed once, even if referenced multiple times
3. Debugging — Isolate and test parts of complex formulas
4. Maintenance — Change logic in one place

Variable Rules

• Variables are evaluated when defined, not when referenced
• Variables are immutable — once defined, they cannot be changed
• Variable scope is limited to the RETURN expression
• Multiple variables can be defined in sequence

Quick Measures

Quick Measures generate DAX formulas from a guided wizard:

Home tab → Quick Measure

Available calculations include:

• Running total, Moving average
• Year-to-date total, Year-over-year change
• Per category averages
• Weighted averages
• Filtered values

Exam Tip: Quick Measures are useful for learning DAX patterns. Examine the generated DAX to understand how the formulas work.

On the Exam

The PL-300 frequently tests:

• Differentiating between measures and calculated columns
• Understanding filter context and how it affects measure results
• Creating basic aggregation measures (SUM, COUNT, AVERAGE, DISTINCTCOUNT)
• Using VAR/RETURN for complex calculations
• Knowing why explicit measures should replace implicit measures