Mastering DP-600 Exam: DAX For The Win Part 1 - Variables and Iterators

This article is part of the series related to mastering the DP-600 certification exam: Implementing Analytics Solutions Using Microsoft Fabric

Disclaimer: The official skill measured in the DP-600 exam is called: Write calculations that use DAX variables and functions, such as iterators, table filtering, windowing, and information functions. However, I’ve decided to split this into two separate articles – in this one, I’ll cover variables and iterators, whereas in the next one, I’ll cover Window and Information functions

“DAX is simple, but not easy!” – famously said Alberto Ferrari, when asked which best describes Data Analysis Expression language. And, that’s probably the most precise definition of the DAX. It may look very easy at first glance, but understanding nuances and how DAX really works, requires a lot of time and “try and fail” cases.

Obviously, this article is not a deep-dive into DAX internals and will not go into these nuances, but it will (hopefully) help you to get a better understanding of the few very important concepts that will make your DAX journey more pleasant and assist you in preparing the DP-600 exam.

Variables in DAX

As a DAX newbie, it’s easy to fall into the trap of thinking that you don’t need variables. Simply said, why would you care about the variables when your DAX formulas consist of one or two lines of code?!

However, as time goes by, and you start writing more complex calculations, you’ll start to appreciate the concept of variables. And, when I say more complex calculations, I mean using nested functions, and possible reusing of the expression logic. Moreover, in many cases, variables may significantly improve the performance of your calculation, as the expression will be evaluated by the engine only once, instead of multiple times!

Finally, using variables enables you to easily debug the code and verify results for the certain part(s) of your formula.

Here is a simple example of using variables in your DAX code:

As you may notice, defining variables requires the usage of the var keyword before the expression is evaluated and assigned to a specific variable.

Of course, the example above is fairly simple, but let’s imagine that we want to display YoY Variance as a percentage. We can write a measure like this:

The first thing to spot here is that we are repeating exactly the same expression for calculating the sales amount for the previous year. We could’ve written the same measure this way:

DAX Iterators

Unlike aggregators, which aggregate all the values from the specific column and return a single value, iterators apply expression for each row of the table they are operating on!

Therefore, the first difference between the two is that iterators need (at least) two parameters to work – the first is always a table that they need to iterate on (both physical or virtual table), and the second is the expression that needs to be applied for every row of that table.

The most common iterator functions are actually the “relatives” of the aggregator functions:

*Aggregator Function*	*Iterator Function*
SUM	SUMX
AVERAGE	AVERAGEX
MIN	MINX
MAX	MAXX

As you see, iterator functions have the letter X in the end and that’s the easiest way to recognize them within the DAX formula. However, it’s an interesting fact that aggregator functions are internally translated by the engine to the iterator function too! So, when you write something like:

Sales Amount = SUM('Online Sales'[SalesAmount])

It’s internally translated to:

Sales Amount = SUMX('Online Sales',
                     'Online Sales'[SalesAmount]
                 )

Please keep in mind that whenever you want to write an expression that includes more than a single column, you MUST use iterators!

The key thing to understand with iterator functions is the context in which they are operating. As they are iterating row by row, the expression is evaluated in the row context, similar to the calculated column DAX formula. However, the table is evaluated in the filter context, which means that if, let’s say, there is an active filter on the ‘Online Sales’ table to show only the data from the year 2019, the final result will include the sum (assuming that you’re using SUMX iterator function) over the rows from the year 2019.

Sales Amount Iterator = 
                    SUMX (
                        FactResellerSales,
                        FactResellerSales[OrderQuantity] * FactResellerSales[UnitPrice]
                    )

The final warning regarding the iterator functions: be careful when using complex iterator functions over large amounts of data, as they are being evaluated row-by-row, and may cause performance issues in some cases.

Conclusion

Keep repeating: “DAX is simple, but not easy!”…As you may’ve seen, DAX offers you a quick entry into the world of (im)possible – where you can perform all kinds of calculations, but you need to be aware of the language nuances and understand how the engine really works in the background.

I strongly suggest following the SQL BI channel and blog, or even better, reading a “DAX bible“: The Definitive Guide to DAX, 2nd edition.

There are also other fantastic resources on the web for learning DAX, such as the Enterprise DNA channel, or Brian Grant’s awesome series of videos, called “Elements of DAX“.

Thanks for reading!