Writing measures in DAX is rather a breeze to learn and implement. However, of course, you have already made out that DAX doesn’t always return the desired results. And also that, more complicated calculations are impossible with basic measures for instance SUM () or COUNTROWS (). To understand this, we need to take a look at the way DAX calculates values.  First of all, we should begin by refreshing some key principles in DAX for Power BI.

What is DAX?

Let’s begin by brushing up on some basics. DAX’s full form is Data Analysis Expressions and serves in:

• Power Pivot for Excel,
• Power BI,
• Tabular model of the SQL Server Analysis Services (SSAS) as a functional calculation language.

By use of the DAX, it is plausible to carry out basic sum calculations apart from more complicated calculations for example – forecasts. Because DAX has acquired some functions of Excel, even though not 1:1, it is easy for most Excel users to make the first steps in DAX.

How DAX Calculate Values?

As mentioned already DAX is easy to learn. However, as a pupil of Marco Russo, DAX Maestro and MVP, once said: “DAX is simple, yet difficult.” This expression says everything!

It is easy to make a simple summation utilizing SUM (). However, already for this function, it needs a knowledge of the calculation method in DAX. What’s more, if you don’t perceive how DAX calculate values, it will be tough to take the next step. Let’s look at this together.

There are three basic rules that we must take into account.

• Rule 1 – Every cell is calculated separately
• Rule 2 – Filter Context
• Rule 3 – Step By Step Calculation

How Does DAX Calculate the Result?

DAX never calculates results the way humans do. Having said that, DAX won’t calculate the value using standard criteria, but first ascertains 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.

In Power BI, the report filters, page filters along with visual filters too transform the filter criteria. So, you have six options to change the filter criteria:

• Report filter,
• Page filter,
• Visual filter,
• Slicers,
• Row header,
• Column header

Summing Up

In this article you learned, the process with which DAX calculate values, which is pretty opposite to how we humans do. Using basic measures the Filter Context can only be affected by these six influencing factors, like, by selecting another entry in the slicer or by modifying the table filter or the row and column headers. And it is here CALCULATE () comes in. CALCULATE () is the single DAX function that can transform or reset the initial Filter Context. That’s the reason, so it is also known as the best friend of a DAX user. You can consider CALCULATE () as a SUMIFS () function which will do a lot more than the equivalent in Excel.

The post The Way Dax Calculate Values – The Fundamental Criteria Followed appeared first on Magnetoz.