1. Iterating functions
Hi again! Another powerful set of DAX functions are the so called iterating functions.
2. Iterating functions
Iterating functions operate on a row-level (that is, the row context) to iterate over each row in a table to perform some sort of expression. Typically, iterating functions end with an X, such as SUMX() or AVERAGEX(), where the X stands for eXpression. Because you can specify an expression instead of a single column, iterating functions allow for more advanced calculations compared to aggregating functions such as SUM() or AVERAGE().
3. Iterating functions: SUMX()
To illustrate how iterating functions work, let's revisit our calculated column example from chapter one, where we calculated Cost by subtracting the Profit column from the Sales column. This newly created column was then utilized to calculate the total costs using SUM(). Note that this required creating one column and one measure.
4. Iterating functions: SUMX()
The same result can be achieved with one line of DAX, while also simplifying the data model. The SUMX() function takes the Fact table as first argument. From there, the expression (Sales minus Profit), is performed at the row context. The end result is the same, but without having to create an intermediate column.
5. Filtering iterating functions
In order to control the filtering aspect of this function, we typically use a filter function within the iterating function in order to filter down our desired table to our desired values.
In this example we are using the FILTER() function to trim down the Fact_Orders table to only contain the Eastern region. This intermediate table is then passed into the SUMX() function to calculate costs.
6. Filtering iterating functions
When we visualize this Total Costs East SUMX measure, we can see that only the East values are shown due to the FILTER() function taking precedence in the filter context.
7. Iterating functions: RANKX()
Another useful iterating function is the RANKX() function. This function takes a given table and ranks the rows by a given expression.
Take the example where you want to rank the regions by their total costs. The region column is part of the Dim_Sales table, and the intermediate function ALL() is used to evaluate all rows across the table.
8. Iterating functions: RANKX()
The result, split by region, looks like this. Note that if ALL() wasn't included in the calculation, every region would be ranked as one. This is because the function would only rank among the values shown within the query context of the visual, being the filtered region in the table. As a result, every region would be the only (and thus highest) category to rank.
9. Operators in DAX
Lastly, let's discuss briefly the use of operators in DAX.
Comparison operators are used to compare values when a Boolean expression is needed for an argument, such as in a filter.
Note that the “strict equal to” operator returns TRUE when the two arguments have the same value OR are both blank.
10. Operators in DAX
Then there's the text operator for text concatenation, by using the ampersand symbol.
11. Operators in DAX
Finally, logical operators are used to combine boolean expressions. A double ampersand is an AND condition; in the example shown we are evaluating the return of both conditions for city and return. Only if both conditions are TRUE then the entire expression returns TRUE.
The double vertical line represents an OR condition, where either one, two, or both conditions must be TRUE to return TRUE for the entire expression.
IN, followed by curly brackets, checks if an expression matches any value in a list of values.
12. Lesson[Knowledge] IN {"Poor", "Great", "Awesome!"}
Let's test what you remembered from this lesson!