Why DAX powers calculations in Power BI - Why It Works This Way

Overview - Why DAX powers calculations in Power BI

What is it?

DAX, or Data Analysis Expressions, is a formula language used in Power BI to create custom calculations and data analysis. It helps you build measures and calculated columns that transform raw data into meaningful insights. DAX works behind the scenes to power the numbers and visuals you see in your reports.

Why it matters

Without DAX, Power BI would only show raw data without any smart calculations or summaries. DAX lets you answer complex business questions by creating dynamic calculations that update as you explore your data. This makes your reports interactive and insightful, helping you make better decisions faster.

Where it fits

Before learning DAX, you should understand basic Power BI concepts like data tables, relationships, and simple report building. After mastering DAX basics, you can explore advanced topics like time intelligence, row-level security, and optimization techniques.

Mental Model

Core Idea

DAX is the language that turns raw data into meaningful, dynamic calculations inside Power BI.

Think of it like...

Think of DAX like a recipe book in a kitchen: raw ingredients (data) are combined and cooked (calculated) to create delicious dishes (insights) that change depending on what you want to eat (your report filters).

┌─────────────┐
│   Raw Data  │
└──────┬──────┘
       │
       ▼
┌─────────────┐
│    DAX      │  <-- Formulas that mix and transform data
└──────┬──────┘
       │
       ▼
┌─────────────┐
│ Calculations│
│  & Measures │
└──────┬──────┘
       │
       ▼
┌─────────────┐
│  Visuals &  │
│  Insights   │
└─────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding What DAX Is

Concept: Introduce DAX as a formula language designed for data analysis in Power BI.

DAX stands for Data Analysis Expressions. It looks like Excel formulas but is built to work with tables and relationships in Power BI. You use DAX to create new calculations that are not directly in your data, like totals, averages, or ratios.

Result

You know that DAX is a special language for making calculations in Power BI, different from Excel but similar in style.

Understanding that DAX is a language tailored for data models helps you see why it’s powerful for business intelligence.

2

Foundation

How DAX Fits Into Power BI

Concept: Explain where DAX operates inside Power BI and what it controls.

Power BI loads your data into a model with tables and relationships. DAX formulas create calculated columns or measures inside this model. Calculated columns add new data to tables, while measures calculate results on the fly when you use visuals.

Result

You see that DAX is the engine behind dynamic calculations that respond to your report filters and selections.

Knowing the difference between calculated columns and measures is key to using DAX effectively.

3

Intermediate

DAX’s Context: Row and Filter Context

🤔Before reading on: do you think DAX calculations always use the whole table or just parts of it? Commit to your answer.

Concept: Introduce the idea that DAX calculations depend on context, which means the data they see can change based on filters or rows.

DAX uses two main contexts: row context and filter context. Row context means the formula looks at one row at a time, like when creating a calculated column. Filter context means the formula considers filters applied by visuals or slicers, affecting measures dynamically.

Result

You understand that DAX calculations can change depending on what data is visible or selected in your report.

Recognizing context explains why the same DAX formula can return different results in different visuals.

4

Intermediate

Measures vs Calculated Columns in DAX

🤔Before reading on: do you think calculated columns and measures behave the same way in reports? Commit to your answer.

Concept: Clarify the difference between calculated columns and measures and when to use each.

Calculated columns add new data to your tables and are computed when data loads or refreshes. Measures calculate results on demand based on filters and user interaction. Measures are more flexible and efficient for aggregations and summaries.

Result

You can decide when to create a calculated column or a measure depending on your analysis needs.

Knowing this difference helps optimize report performance and accuracy.

5

Intermediate

Basic DAX Functions and Syntax

🤔Before reading on: do you think DAX formulas are exactly like Excel formulas or have unique parts? Commit to your answer.

Concept: Introduce common DAX functions and how to write simple formulas.

DAX has functions like SUM, AVERAGE, and FILTER similar to Excel but also has unique ones like CALCULATE that change filter context. Formulas start with an equals sign and can combine functions and operators to create powerful calculations.

Result

You can write simple DAX formulas to create measures and calculated columns.

Understanding DAX syntax and functions is the foundation for building complex calculations.

6

Advanced

How DAX Handles Filter Propagation

🤔Before reading on: do you think filters in one table automatically affect related tables in DAX? Commit to your answer.

Concept: Explain how DAX uses relationships to propagate filters between tables during calculations.

DAX respects relationships in your data model. When you filter one table, DAX automatically filters related tables based on those relationships. This filter propagation allows measures to calculate correctly across multiple tables without extra formulas.

Result

You understand how DAX dynamically filters data across tables, enabling complex multi-table calculations.

Knowing filter propagation prevents confusion when results change unexpectedly due to relationships.

7

Expert

DAX’s VertiPaq Engine and Query Optimization

🤔Before reading on: do you think DAX formulas run directly on raw data or use a special engine? Commit to your answer.

Concept: Reveal how DAX calculations are executed by the VertiPaq engine inside Power BI for speed and efficiency.

Power BI stores data in a compressed, columnar format called VertiPaq. When you write DAX, it translates formulas into queries that VertiPaq runs very fast using in-memory compression and indexing. Understanding this helps you write efficient DAX that avoids slow queries and large memory use.

Result

You appreciate that DAX is not just a formula language but part of a powerful engine optimized for big data analysis.

Knowing the VertiPaq engine’s role guides you to write performant DAX and troubleshoot slow reports.

Under the Hood

DAX formulas are parsed and converted into query plans that run on the VertiPaq in-memory engine inside Power BI. VertiPaq stores data in compressed columns and uses indexes to quickly filter and aggregate data. DAX uses filter and row context to determine which data to include in calculations dynamically. Measures are evaluated at query time, while calculated columns are computed during data refresh.

Why designed this way?

DAX was designed to handle large datasets efficiently by leveraging in-memory storage and columnar compression. This design allows fast, interactive analysis even on millions of rows. The language’s focus on filter context and relationships reflects the needs of business users to slice and dice data flexibly without complex SQL queries.

┌───────────────┐
│   User Query  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│   DAX Parser  │  <-- Converts formulas into query plans
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ VertiPaq Engine│  <-- Runs queries on compressed data
│ (In-memory DB) │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│  Calculation  │  <-- Applies filter & row context
│   Results     │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think DAX is just like Excel formulas? Commit to yes or no.

Common Belief: DAX is just Excel formulas copied into Power BI.

Reality: DAX is a distinct language designed for relational data models with concepts like filter context and relationships that Excel formulas don’t have.

Why it matters: Treating DAX like Excel leads to wrong formulas and unexpected results in reports.

Quick: Do you think calculated columns and measures calculate at the same time? Commit to yes or no.

Common Belief: Calculated columns and measures behave the same and calculate when you open the report.

Reality: Calculated columns compute during data refresh and become static, while measures calculate dynamically based on user interaction.

Why it matters: Confusing these causes performance issues and incorrect data analysis.

Quick: Do you think DAX ignores relationships when filtering data? Commit to yes or no.

Common Belief: DAX calculations only look at the table they are written in, ignoring relationships.

Reality: DAX automatically uses relationships to propagate filters across tables during calculations.

Why it matters: Ignoring this leads to wrong assumptions about data filtering and incorrect results.

Quick: Do you think all DAX functions are fast and efficient? Commit to yes or no.

Common Belief: All DAX functions perform equally well regardless of data size.

Reality: Some DAX functions can cause slow queries or high memory use if not written carefully, especially on large datasets.

Why it matters: Not optimizing DAX can make reports slow and frustrating for users.

Expert Zone

1

DAX’s CALCULATE function changes filter context in subtle ways that can override existing filters, which experts use to build complex dynamic measures.

2

Understanding how DAX’s evaluation context switches between row and filter context is key to debugging tricky formulas that behave differently in visuals versus calculated columns.

3

The VertiPaq engine’s compression and dictionary encoding mean that column data types and cardinality greatly affect performance, influencing how experts design data models.

When NOT to use

DAX is not suitable for heavy data transformation or cleaning tasks; use Power Query or SQL for those. Also, for extremely large datasets beyond in-memory limits, consider aggregations or direct query mode instead of complex DAX.

Production Patterns

Experts use DAX to create reusable measures for KPIs, implement time intelligence for trend analysis, and build dynamic segmentation. They also optimize DAX by minimizing row context transitions and using variables to improve readability and performance.

Connections

SQL Query Language

DAX builds on similar ideas of filtering and aggregating data but works inside an in-memory model rather than a relational database engine.

Knowing SQL helps understand DAX’s filter and aggregation logic, but DAX adds dynamic context awareness that SQL lacks.

Functional Programming

DAX formulas resemble functional programming with pure functions and no side effects, focusing on expressions rather than commands.

Understanding functional programming concepts clarifies why DAX formulas are declarative and how context flows through calculations.

Cooking Recipes

Like recipes combine ingredients to create dishes, DAX combines data columns and filters to create insights.

This connection helps appreciate how changing inputs or filters changes the final result dynamically.

Common Pitfalls

#1 Using calculated columns when a measure would be better.

Wrong approach:

Total Sales = SUM(Sales[Amount]) // created as a calculated column

Correct approach:

Total Sales = SUM(Sales[Amount]) // created as a measure

Root cause:

Misunderstanding that calculated columns are static and measures are dynamic, leading to unnecessary data bloat and poor performance.

#2 Ignoring filter context in measures causing wrong totals.

Wrong approach:

Average Sales = AVERAGE(Sales[Amount]) // without considering filters

Correct approach:

Average Sales = CALCULATE(AVERAGE(Sales[Amount])) // respects current filters

Root cause:

Not realizing that some functions need CALCULATE to apply filter context properly.

#3 Writing complex nested IF statements instead of using DAX functions.

Wrong approach:

Category = IF(Sales[Amount] > 1000, "High", IF(Sales[Amount] > 500, "Medium", "Low"))

Correct approach:

Category = SWITCH(TRUE(), Sales[Amount] > 1000, "High", Sales[Amount] > 500, "Medium", "Low")

Root cause:

Not knowing more readable and efficient DAX functions like SWITCH.

Key Takeaways

DAX is a powerful formula language designed specifically for dynamic data analysis in Power BI.

Understanding filter and row context is essential to writing correct and efficient DAX calculations.

Measures calculate results on the fly based on user interaction, while calculated columns are static and computed during data refresh.

DAX works closely with Power BI’s VertiPaq engine to deliver fast, interactive reports even on large datasets.

Mastering DAX unlocks the full potential of Power BI to create insightful, flexible, and performant business intelligence solutions.