Why intermediate DAX solves business questions in Power BI - Why Use It

Introduction

Intermediate DAX helps you answer more complex business questions by allowing you to create calculations that consider context and relationships in your data. It goes beyond simple sums or counts to provide insights like comparisons, trends, and conditional results.

When you need to compare sales performance between different time periods, like this year versus last year.

When you want to calculate running totals or cumulative values over time.

When you need to filter data dynamically based on user selections or specific conditions.

When you want to create ratios or percentages that depend on other calculated values.

When you need to analyze data across related tables, like customers and their orders.

Steps

Step 1: Open

- Power BI Desktop

The main report canvas and data model view appear

Step 2: Click

- Modeling tab > New measure

A formula bar opens to write a DAX expression

Step 3: Type

- Formula bar

The measure formula is entered, for example: Total Sales LY = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Calendar'[Date]))

💡 Use IntelliSense suggestions to help write correct DAX syntax

Step 4: Press

- Enter key

The new measure is created and appears in the Fields pane

Step 5: Drag

- New measure to a visual on the report canvas

The visual updates to show the calculated result based on the measure

Before vs After

Before

Report shows only total sales as a simple sum without time comparisons

After

Report shows sales compared to last year using an intermediate DAX measure, enabling trend analysis

Settings Reference

Data type

📍 Modeling tab > Properties pane

Defines the type of data the measure returns to ensure correct calculations and formatting

Default: Decimal Number

Format

📍 Modeling tab > Properties pane

Controls how the measure's result is displayed in visuals

Default: General

Summarization

📍 Fields pane > Measure properties

Determines how the measure aggregates data when used in visuals

Default: Sum

Common Mistakes

Using simple SUM instead of CALCULATE with time intelligence functions

Simple SUM does not consider time context, so it cannot compare periods correctly

Use CALCULATE with SAMEPERIODLASTYEAR or other time functions to adjust filter context

Writing DAX without understanding filter context

Results may be incorrect or unexpected because DAX calculations depend on current filters

Learn how filter context works and use functions like ALL or FILTER to control it

Summary

Intermediate DAX enables solving complex business questions by using context-aware calculations.

It allows comparisons, dynamic filtering, and calculations across related tables.

Understanding filter context and time intelligence functions is key to writing effective DAX.