🧮 The Brain of Power BI: DAX

So far, we have cleaned data using Power Query and visualized it using charts. But what if you want to calculate something that doesn't exist in your raw data? For example, your data has Sales and Costs, but you want to calculate Profit Margin %.

You must write a formula. In Power BI, the formula language is called DAX (Data Analysis Expressions).

DAX looks very similar to Excel formulas, but it is vastly more powerful because it operates on entire tables and columns, not individual cells.

🧱 DAX Calculation Types

There are three ways you can write DAX in Power BI. Understanding the difference is the most important step in mastering DAX.

1. Calculated Columns

A Calculated Column evaluates row-by-row. If your table has 1 million rows, the DAX formula calculates 1 million times and creates a brand-new physical column in your table.

Example: Profit = 'Sales'[Revenue] - 'Sales'[Cost]
When to use: ONLY use calculated columns if you need to use the result in a Slicer (filter) or on the Axis of a chart (e.g., categorizing a customer as "High Income" or "Low Income").
Warning: Because they physically store data in your model, they bloat file size and slow down performance.

2. Measures (The Gold Standard)

A Measure is a dynamic calculation that happens instantly, on-the-fly, when a user interacts with the report. It does not create a physical column in your database. It just sits in the background as a "rule" until you drag it onto a chart.

Example: Total Profit = SUM('Sales'[Profit])
When to use: Use measures for all aggregations (Math, Totals, Averages, Percentages). Because they calculate on the fly, they take up zero storage space!

3. Calculated Tables

You can write a DAX formula that generates an entirely new table based on existing data in memory. (e.g., creating a dedicated Calendar table).

👻 Implicit vs Explicit Measures

When you drag a raw number column (like SalesAmount) onto a Bar Chart, Power BI automatically creates a temporary Sum for you. This is an Implicit Measure.

Best Practice

Never rely on Implicit Measures! Always right-click your table and select "New Measure" to manually write your own DAX (e.g., Total Sales = SUM(Sales[SalesAmount])). This is called an Explicit Measure. Explicit measures can be reused inside other formulas to build Measure Trees (where one master measure feeds into 5 other measures), keeping your code clean and organized.

🧠 The Hardest Concept: Context

When DAX evaluates a formula, it does not work in isolation. The result depends on the Context in which the formula is being calculated. Understanding Context is the key to understanding DAX.

1. Row Context

Row Context means DAX knows the current row it is working on.

Calculated Columns automatically have Row Context.
When DAX calculates Revenue - Cost, it evaluates the formula separately for every row.
Measures do not have Row Context by default because they work with groups of rows rather than a single row.

Example:

DAX

Profit = Sales[Revenue] - Sales[Cost]

For each row, DAX uses only that row's Revenue and Cost values.

2. Filter Context

Filter Context is the collection of filters applied to a calculation.

These filters can come from:

Visuals
Slicers
Page Filters
Report Filters
Other DAX calculations

Imagine you create a measure:

DAX

Total Sales = SUM(Sales[Amount])

In a Card Visual, it might return $10 Million (the grand total).
In a Bar Chart grouped by Country, the same measure returns different values for each country because each bar applies a different filter.

The formula never changes. Only the Filter Context changes.

3. Context Transition

Context Transition occurs when DAX converts an existing Row Context into a Filter Context.

This usually happens when the CALCULATE() function is used.

DAX

CALCULATE([Total Sales])

When CALCULATE() is executed inside a row-by-row operation, DAX takes information from the current row and applies it as a filter before performing the calculation.

This is one of the most important advanced DAX concepts and is the foundation of many complex calculations.

🎯 Quick Memory Trick

text

Row Context        = Current Row
Filter Context     = Visible Rows
Context Transition = Row → Filter (via CALCULATE)

🏋️ Practice Drill

Let's write your first DAX Measure!

Task:

text

// Try answering these:
1. In the **Report View**, go to your Data Pane on the right.
2. Right-click the name of your table and select **New Measure**.
3. A formula bar will appear at the top of the screen (just like Excel).
4. Type exactly this: `Total Count = COUNTROWS('YourTableName')` *(replace 'YourTableName' with the actual name of your table!)*. Hit Enter.
5. Notice that a new field appeared in your Data pane with a tiny calculator icon next to it! This is your Measure.

💡 Click for Solutions

Drag your new Total Count measure onto the blank canvas, and change the visual to a "Card". Did it tell you exactly how many rows are in your table? Now, add a Slicer to the page and click a filter. Watch your Measure instantly recalculate based on the new Filter Context! You are now a DAX programmer!

← 🎛️ Advanced Visuals and Interactivity | Next Topic → 🔢 Core DAX Functions