DAX Formulas in Power BI: A Practical Guide

When a driver who has spent years with an automatic gearbox gets behind the wheel of a manual, the familiar moves suddenly stop working. Excel and Power BI feel much the same: SUM, IF and FILTER look familiar, but the calculation logic is different. DAX formulas in Power BI operate on row and filter context — and that is exactly what requires a change of habits.

In this guide we look at what DAX is, how it differs from Power Query, which functions you need first and where the typical mistakes come from. The material suits both DAX for beginners and experienced analysts.

What DAX is and why you need it

DAX is Microsoft’s formula and expression language for Power BI, Analysis Services and Power Pivot in Excel. DAX is used to create measures, calculated columns and tables. The result of a calculation depends on the structure of the model and on the context in which the formula is evaluated.

Excel formulas usually refer to cells and ranges, whereas DAX formulas work with the tables and columns of the model. A measure based on SUM, for instance, is recalculated according to the filters currently applied in the report.

Why an analyst needs DAX:

If a company uses Power BI for business, DAX is what provides custom metrics and calculations that change together with the report filters.

DAX vs Power Query: what is the difference

Power Query prepares and transforms the data before it is loaded into the model, while DAX performs calculations inside the model.

Power Query is responsible for preparing the data, and DAX functions for the calculations inside the model. A syntactically correct DAX formula can still return a wrong result because of an error in the logic, the relationships or the context.

Core DAX functions

There is no need to learn all DAX functions at once. It is more important to master the basic ones first and understand how context affects a calculation.

CALCULATE

CALCULATE is one of the key DAX functions: it evaluates an expression in a modified filter context.

Basic syntax:

CALCULATE(<expression>, <filter1>, <filter2>, …)

Example: you need to calculate the sales of the “Electronics” category even if another category is selected in the report through the Product[Category] field:

Electronics Sales = CALCULATE(SUM(Sales[Amount]), Product[Category] = “Electronics”)

CALCULATE replaces the current filter on Product[Category], but the filters by period, region and so on stay in effect. This is one of the basic principles behind more complex DAX formulas.

SUM, SUMX and iterator functions

SUM is the simplest aggregation function; it sums the values of a single column:

Total Amount = SUM(Sales[Amount])

For calculations where the expression has to be evaluated for every row, you use the iterator functions SUMX, AVERAGEX and COUNTX. They evaluate the expression row by row and then aggregate the result.

A classic example is revenue, when price and quantity are stored in different columns:

Revenue = SUMX(Sales, Sales[Quantity] * Sales[Price])

SUM does not fit this example, because the values in each row have to be multiplied first.

Time intelligence — formulas for periods

DAX date functions are used to compare periods — month over month, year over year — or to build a running total.

These functions are usually used inside CALCULATE, so that the required figure is calculated on the basis of the returned set of dates.

An example formula for a year-over-year sales comparison:

Sales Last Year = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Calendar[Date]))

Such formulas in Power BI are used in KPI dashboards to compare figures across periods automatically.

Common mistakes when writing DAX

If you are only learning how to write DAX formulas, it is worth starting with the mistakes that directly affect the result of a calculation. Here are the most common DAX mistakes and how to avoid them.

Examples of DAX formulas for business reports

Here are several DAX formulas for typical business reports:

Share of repeat customers:

% Repeat Customers = VAR RepeatCustomers = FILTER( VALUES(Sales[ClientID]), CALCULATE(DISTINCTCOUNT(Sales[OrderNumber])) > 1 ) RETURN DIVIDE( COUNTROWS(RepeatCustomers), DISTINCTCOUNT(Sales[ClientID]) )

A repeat customer here is one with more than one unique order in the current report context.

Running total of sales since the beginning of the year:

Sales YTD = TOTALYTD(SUM(Sales[Amount]), Calendar[Date])

Change versus the previous month (in %):

Sales Previous Month = CALCULATE( [Total Amount], PREVIOUSMONTH(Calendar[Date]) )

MoM Change % = DIVIDE( [Total Amount] – [Sales Previous Month], [Sales Previous Month] )

TOP-N products by revenue (for ranking visuals):

Product Rank = RANKX(ALL(Product[Name]), [Revenue], , DESC)

To get exactly the TOP-N products, apply the corresponding filter to the visual after the ranking is calculated — Product Rank <= 10, for example.

Active customers over the last 90 days:

Active Customers 90d = CALCULATE( DISTINCTCOUNT(Sales[ClientID]), DATESINPERIOD(Calendar[Date], MAX(Calendar[Date]), -90, DAY) )

How IWIS builds complex DAX models for clients

IWIS business intelligence projects include solutions for Planeta Kino, Helen Marlen, BAT, Swiss Krono and other companies. In complex Power BI models the business logic often goes far beyond basic SUM and CALCULATE.

A typical sequence of the team’s work on a DAX model:

Free consultation from IWIS

If DAX formulas return the wrong result, the dashboard is slow or the figures do not add up, what needs checking is the model, the relationships, the filters, the business logic and the formulas themselves.

During a free consultation the IWIS team will: