What is an Iterator?
Iterators are functions in DAX that compute an expression for every row in a table within the given filter context. Most iterators are paired with a standard DAX function, for example SUMX, the iterator, has SUM as its standard function. Standard functions like SUM simple sum the column you specify and do not allow for expressions as part of the argument. In addition, SUM does not allow you to control the filter context whereas SUMX provides that control.
When is an Iterator useful?
Iterators are most useful when creating measures that require additional computation such as revenue. To create a revenue measure you would multiply price by quantity and then sum the result under the given filters and slicers. You may say, well I can just create a calculated column for the revenue and then SUM that column. You could! But keep in mind that calculated columns add data to your model for every row in the table you add them to. Measures on the other hand only require data for the formula and then they are calculated as needed. By using an iterator in your measure you can save yourself valuable bytes in your report and speed up the refresh of your visuals.
A word of caution.
Iterators require an understanding of what is called evaluation context to be used correctly. We don’t have time to review all you need to know about evaluation context in this article alone, but I will give an example that will get you started on the right path. The RANKX formula requires two arguments, a table and an expression. Three other arguments are optional, such as whether you want to rank ascending or descending. We will not worry about the optional arguments here. We will also use the sample data below (Sales Amount is measure in this case using SUMX):
Country | Sales Amount | Rank |
USA | $ 400 M | |
United Kingdom | $ 300 M | |
Canada | $ 1 B | |
Mexico | $ 250 M | |
Cuba | $ 325 M | |
Table: Sales
You would probably start out by writing this DAX code
RANKX( ‘Sales’, [Sales Amount] )
But you would get the following result:
Country | Sales Amount | Rank |
USA | $ 400 M | 1 |
United Kingdom | $ 300 M | 1 |
Canada | $ 1 B | 1 |
Mexico | $ 250 M | 1 |
Cuba | $ 325 M | 1 |
This is not correct. The reason the formula isn’t working as hoped is because the RANKX function is iterating row by row within the given filter context. The table adds to the filter context so for the row with USA as the country, only data from the USA is included and then ranked to itself and thus the Sales Amount ranks 1 within the USA filter context and for each other country.
To correct our formula we need to use the ALL function to remove the unwanted filter context.
RANKX( ALL( ‘Sales’ ), [Sales Amount] )
This will give us the result we are looking for.
Country | Sales Amount | Rank |
USA | $ 400 M | 2 |
United Kingdom | $ 300 M | 4 |
Canada | $ 1 B | 1 |
Mexico | $ 250 M | 5 |
Cuba | $ 325 M | 3 |
Remember, iterators are a powerful and efficient way to build measures, but they require some knowledge of evaluation contexts to use correctly.
If you need any help setting this up, or anything Power BI related, contact a JourneyTEAM solutions specialist today.