Stop 07 · BI Tools & DashboardsTopics · 5/9

DAX Basics (Power BI)

In short

Drag and drop gets you totals. Anything smarter, like share of total, year-on-year growth or margin, needs DAX. The good news: a handful of functions covers most daily work.

Data Analysis Expressions — Power BI's formula language for calculated columns and measures, similar in spirit to Excel formulas but operating across an entire data model.

The jaadu, dressed as a tailor, measures cloth beside a big calculator.

Think of it like this

The tailor's rule

A tailor's rule says 'sleeve = arm length plus 2 inches'. The rule is written once, but the answer depends on who's standing in front. A DAX measure is that rule. Its result changes with whatever the chart or slicer is filtering at that moment.

Watch it happen

One measure, different filter contexts

Pune 600

Delhi 600

Goa 400

Total 1600

1/5A table visual with one row per city. Total Sales = SUM(Sales[Amount]) is a single measure.

The big ideas

What you need to know

Measures, not columns

Measures calculate at query time for the current filters. Calculated columns compute once per row and take memory.

Filter context

Slicers, rows and chart axes all set filters. Every measure is evaluated inside them.

CALCULATE changes context

CALCULATE(measure, filters) evaluates with filters added or removed. It's the heart of DAX.

A proper date table

Time functions like SAMEPERIODLASTYEAR need a continuous date table marked as such.

See it in code

Walk through the code

dax

1Total Sales = SUM ( Sales[Amount] )2 3Share of All Cities =4DIVIDE (5    [Total Sales],6    CALCULATE ( [Total Sales], ALL ( Sales[City] ) )7)8 9Sales LY =10CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )11 12YoY % = DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] )

1/4A basic measure. Its result depends on whatever filters apply where it's used.

Four measures that cover a surprising amount of reporting: total, share of total, last year and growth.

Save yourself the pain

Common mistakes

Oops

Making a calculated column for profit margin, then summing it.

Do this instead

Make a measure: DIVIDE(SUM(Profit), SUM(Sales)). Ratios must be calculated after aggregation.

Oops

Using the / operator and getting errors on blank months.

Do this instead

Use DIVIDE(a, b), which returns blank instead of an error when b is zero or empty.

Your mission

Four measures

~30 min

Quick check

Why does the same Total Sales measure show 600 in the Pune row and 1600 in the total row?

Go deeper

1Write a simple DAX measure (e.g. total sales) and a calculated column.
2Learn the difference between a calculated column (row-by-row) and a measure (aggregated).
3Practice a slightly more advanced DAX measure using CALCULATE().