Hits: 5858
Writing measures in DAX is relatively easy to learn. But surely you have already noticed that DAX does not always return the desired results and that more complex calculations are not possible with simple measures like SUM () or COUNTROWS ().
To understand this, we need to look at the way how DAX calculates values.
But first, let’s start by refreshing some fundamental principles in DAX for Power Pivot and for Power BI.
If you want to read more about Power BI, subscribe to our newsletter.
You can read more about Power BI here.
What is DAX?
Let’s start by refreshing some fundamentals.
DAX stands for
Data Analysis eXpressions and serves in
- Power Pivot for Excel,
- Power BI and
- Tabular model of the SQL Server Analysis Services (SSAS)
as a functional calculation language.
Using DAX, it is possible to perform simple sum calculations as well as highly complex calculations such as forecasts.
Since DAX has taken over some functions of Excel, even if not 1:1, it is easy for many Excel users to make the first steps in DAX.
Where and when is DAX applied?
In the development phase of reports, data are first imported and edited using Power Query. This is followed by the creation of DAX formulas and their use in reports. Finally, the reports are published to the Power BI Service.
The following picture illustrates this.
How DAX calculates values?
I mentioned at the beginning that DAX is easy to learn. But as a student of Marco Russo, DAX Maestro and MVP, once said:
DAX is simple, but not easy”
Student of Marco Russo
This statement already says it all!
It is easy to make a simple summation using SUM (). But already for this function, it requires an understanding of the calculation method in DAX. And if you don’t understand how DAX calculates values, it will be hard to go any further. We will now look at this together.
The following table was created using Power BI.
The results are correct but may be surprising you at first glance.
Maybe you’ve never thought about how DAX calculates the individual results. But it is essential to develop an understanding of it.
There are three basic principles that we must take to heart.
Principle 1 – Each cell is calculated individually
Let’s look at the results of the “Sales to males from Switzerland” measure. DAX has calculated that the revenue in France and Switzerland is 23’007.15 each. But the overall result is also 23’007.15. How?
The overall result is NOT the sum of the two individual values, but the result of the individual calculation of this cell, considering the criteria applied!
Principle 2 – Filter Context
This leads us to the question, how exactly was this individual calculation carried out? What were the applied criteria?
First the coordinates of the table are used as filter criteria, then the calculation of the cell is carried out!
In other words: First Filter Context, then calculation
Principle 3 – Step by Step calculation
Let’s take a step-by-step look at the process.
DAX applies 4 steps to calculate the desired result per cell.
1. Filter – Evaluate Filters on table or visual (Initial Filter Context)
2. CALCULATE () – Apply Calculate Filter (if applicable)
3. Relationships – Apply relationships (in filter direction)
4. Arithmetic – Calculate the result, according to the formula (Sum, etc.)
The four letters marked in bold are abbreviated as follows
FC RA
Step by Step in deepth
Step 1 – Evaluate Initial Filter Context
Let’s look at the Initial Filter Context. I have simplified the previous example.
The DAX measure for the example is:
Total Sales = SUM('Sales'[SalesAmount])All the measure does, is to sum up the column named “SalesAmount” in the table “Sales”. But how does DAX get the red marked value “30’826.72”?
How do humans calculate the result?
As humans, we usually interpret the value as
This is correct.
In the three data slicers on the left side the values “single”, “France”, “Switzerland” and “2013” are selected. The column heading is “male”, the row heading is “Switzerland”.
How does DAX calculate the result?
DAX doesn’t calculate results like humans do. That said, DAX does not calculate the value this way, but first determines the corresponding filter criteria from the coordinates of the table and applies them together with the selected filters from the slicers to the data tables.
For DAX, the value “30’826.72” is the result of the following conditions:
The coordinates of the table and the selected slicer values were converted into filter criteria.
In Power BI, the report filters, page filters as well as the visual filters also change the filter criteria. So, there are six possibilities to change the filter criteria:
- Report filter,
- Page filter,
- Visual filter,
- Slicers,
- Row header,
- Column header
Altogether, we call this the
“Initial Filter Context”
This is now applied to the underlying dimension tables, which can be made visible looking at the “Customers” table.
There are 18’484 rows in the table. Using the visual filter possibilities, we may simulate the Initial Filter Context on the Customer table.
DAX scans the entire “Customers” table and checks row by row whether the filter criteria are met or not. Each line that does not meet the criteria is virtually deleted until only the rows that meet all the criteria remain. In our example, 5’266 rows remain.
Step 2 – Apply CALCULATE()
We skip this step for now, because in our example, we didn’t use the CALCULATE () function. Remember, we just used
Total Sales = SUM('Sales'[SalesAmount])I’ll get back to this in the next article.
Step 3 – Relationships (Filter Propagation)
In a well modelled data model, the descriptive data (called dimension table) are separated from the transaction data (fact tables) and then linked by relationships.
In our little data model, you may see the relationships by clicking on the “Model” tab on the left side of the Power BI Desktop.
There you’ll see a line and an arrow, pointing from the table “Customers” to the table “Sales”. The arrow indicates the direction of the filter (from “Customers” to “Sales”, the number (1) denotes the One-Side of the relationship, the Asterix the Many-Side. The two highlighted columns (CustomerKey) are linked by the relationship.
A Filter Context propagates itself automatically through relationships, according to the cross filter direction of the relationship. A filter applied to the One-Side of a relationship affects the rows of the table on the Many-Side of that relationship, but the filter does not spread in the opposite direction.
So, by using this relationship, the filtered dimension tables, such as the “Customers” table, are applied to the fact tables (“Sales”) in the third step. Thus, we call the Filter Propagation.
What does that mean?
The entire fact table “Sales” with 1’662 data records is now also filtered.
And in such a way that only the data records with those customers, which meet all the criteria of the filtered rows of the Customer table, remain. The result is also a virtual table “Sales”, which only contains the surviving entries.
Of the 1’662 records there are still 24 records left.
Step 4 – Arithmetic
The last step is to calculate the result, according to the formula
Total Sales = SUM('Sales'[SalesAmount])Now, the measure sums up the remaining 24 entries in the Sales[SalesAmount] column. The result is “30’826.72”, which is now displayed in the corresponding cell in the table.
If you want to know more details about the calculation method, I recommend the book
“Power Pivot and Power BI: The Excel User’s Guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016“
by Rob Collie. He has dedicated the entire chapter 7 with detailed descriptions to this topic. By the way, I do not receive any payment for this recommendation.
…stay tuned
In this article, we learned, how DAX calculates values and we saw, that the coordinates on the table (row headers, column headers), the selected values in the slicers, the report filters, the page filters and the visual filters are converted into filter criteria. This Filter Context finally determines the return value that is displayed in the table.
However, with simple measures the Filter Context can only be influenced by these six influencing factors, for example by selecting another entry in the slicer or changing the table filter or the row and column headers.
And this is where CALCULATE() comes in. CALCULATE() is the only DAX function that can change or reset the initial Filter Context. Therefore, it is also called
“the best friend of a DAX user”.
You can think of CALCULATE () as a SUMIFS () function that can do much more than the equivalent in Excel. But for the moment, I’ll skip this topic and will write about CALCULATE() in the next article.
This brings us to the end of the first part. I explained how DAX works. In the next part I’ll introduce CALCULATE () and will show, how you can apply your own Filter Criteria.
You can read more about Power BI here.
Happy Querying!
