Mastering Advanced DAX Concepts

Oct 2, 2024

Lecture Notes: Advanced DAX Concepts

Session Overview

  • Session Repeat Announcement
    • Morning session is repeated at 4:40 p.m. and again tomorrow morning at 10:10 a.m.
    • Focus on introduction to DAX for those who missed it.

Advanced DAX Introduction

  • Session Expectations

    • Not a 400-level session but a continuation of DAX introduction.
    • Topics include evaluation context, row context, filter context, calculations, and iterations.

  • Presenter: Alberto Ferrari

    • Consultant with SQLBI
    • Founder of sqlbi.com, a resource for learning about DAX.
    • Reference material: "Definitive Guide to DAX"

Key Concepts in DAX

Evaluation Contexts

  • Evaluation Context
    • The most challenging part of DAX to grasp.
    • Consists of row context and filter context.
    • Evaluation context affects the visibility of data for calculations.

Filter Context

  • Determines which subset of data is visible for calculations.
  • Examples:
    • Slicer filters data subset seen by a report or a pivot table.

Row Context

  • Iterating over a table row-by-row.
  • Can be introduced by calculated columns or iterators (e.g., SUMX, AVERAGEX).
  • Needed to evaluate column values.

Practical Examples

Calculating Rankings

  • Task: Compute a ranking for products based on list price.
    • Count the number of products with a price greater than the current product.
    • Introduce variables to simplify calculations.

Using CALCULATE

  • Function Power

    • Only function that can modify filter context.
    • Syntax: CALCULATE(<expression>, <filters>)
    • Filters are evaluated and logically combined.

  • Example: Calculate sales amount where unit price is greater than 100.

    • Replaces existing filters on specified columns.

Context Transition

  • Concept: CALCULATE transforms row context into an equivalent filter context.
    • Essential when converting row context into filter context for accurate calculations.

Measure vs. Column Context

  • When calling a measure, an implicit CALCULATE is applied, affecting filter context.

Filter Context Propagation

  • Direction
    • In Power Pivot: Propagates from one side to many side only.
    • In Power BI: Can be set to propagate both ways.

Time Intelligence in DAX

  • Requires a complete calendar table.
  • Example: Year-to-date calculation by modifying filter context to include all relevant dates.

Conclusion

  • Focus on mastering evaluation contexts for effective DAX usage.
  • Reference: "Definitive Guide to DAX" for more advanced topics.

These notes cover the essentials from Alberto Ferrari's presentation on advanced DAX concepts. Understanding evaluation contexts and the use of CALCULATE are critical for mastering DAX.