Advanced DAX Functions- Iterators

Hello everyone, let's briefly discuss about the different advanced DAX functions and deep dive into the functionality of the iterator functions.

Some of the advanced DAX functions are:

1.Scalar Functions-  Scalar functions return a single value , rather than a column or table; common examples include aggregation, conversion, rounding, and logical functions.

2.Advanced Calculate Functions-  CALCULATE is a powerful DAX function which is commonly used to modify filter context;  advanced calculate functions include expanded tables , context transition, evaluation.

3.Table and Filter Functions- Table and Filter functions return columns or tables rather than scalar values, and can be used to either generate new data or serve as table inputs within DAX measures.

4.Calculated Table Join Functions-  Calculated table joins are used to combine two or more tables of data; common examples include CROSSJOIN , UNION , EXCEPT and INTERSECT.

5.Relationship Functions- Relationship functions allow you to access fields within DAX measures or calculated columns through either physical or virtual relationships between tables.

6.Iterator Functions: Functions like  CONCATENATEX, AVERAGEX, RANKX are called iterator functions, they evaluate value row wise through a loop to get a scalar value as return.

7.Advanced Time Intelligence Functions: Functions, which allow you to build custom calendars, define dynamic date ranges, and compare performance across specific time periods: like days, week, month, quarter, year, etc.

Iterator Functions

Iterator functions allow you to loop through the same expression on every row of a table in order to evaluate a single scalar value (i.e., max, min, average) or derive a new table
 

Some of the commonly used Iterator functions are:

• AVERAGEX
• RANKX
• CONCATENATEX

We will discuss these three functions one by one.

1.AVERAGEX Function

This function calculates the average (arithmetic mean) of a set of expressions evaluated over a table for each row.

Syntax:

=AVERAGEX (<Table>, <Expression>)

For example: AVERAGEX_Unit Cost = AVERAGEX(Table1, Table1[Unit Cost])

2.RANKX Function

Returns the rank of an expression evaluated in the current context in the list of values for the expression evaluated for each row in the specified table.

Syntax:

=RANKX(<Table>, <Expression> [, Value] [, Order] [, Ties]

For example:  Ranking_Total = RANKX(Table1, Table1[Total], , DESC, Dense)

• “Order” in ranking can take either of the two values- Ascending or descending.

• “Ties” in ranking can take either “Skip” value or “Dense” value. There is not much difference in the two, if you will select “Skip” , then in case of ties (where two values are same and receive same ranking), next rank will be skipped, while the next rank will not be skipped if the “Dense” option is selected.

In this case, “Dense” option was selected, thus we can see that ranking 16 and 17 are not skipped, even when the ranking 15 and 16 are shared by two.

3.CONCATENATEX Function

Evaluates expression for each row on the table, then return the concatenation of those values in a single string result, separated by the specified delimiter.

Syntax:

=CONCATENATEX (<Table>, <Expression> [, <Delimiter>] [, <OrderBy_Expression>] [, <Order>] [, <OrderBy_Expression>] [, <Order>] [, …]]])

For Example: Concatenating total = CONCATENATEX(Table1, Table1[Total], ",")