Blog Article & Copyright by Lukas Hillesheim
In the DAX language, sorting can only be applied at the end - when the data is ready for delivery - but not as an intermediate step, for example, to save sorted data in a variable. The following article shows how this goal can be achieved with the help of GENERATE and GENERATESERIES.
The article uses an example built on the following steps:
- - Step 1: A variable named "set0_RankedCities" is filled with a list of city names, their turnover and a corresponding rank information.
- - Step 2: The content of "set0_RankedCities" is copied to another variable called "set1_SortedCities". The process is done by GENERATE which can conceptually be compared with a ForEach loop. For each cycle the current loop counter is used to find the item that has an corresponding rank. That item is then inserted into the result. Since the ForEach loop causes a search of rank values in ascending order, the result variable is also filled in ascending order. The second variable thus contains the sorted contents of the first variable.
- - Step 3: The content of the second variable, "set1_SortedCities", is presented in a column as a concatenated string. Note that the sorted string could not be generated with ORDER BY.
1. Generate a List with Rank Information
The starting point is an unsorted list of cities, their turnover and a corresponding rank information. The rank information implements the user-defined sort criterion and is later used as an index. Here is the DAX code and output:
Figure 1 - Unsorted List of Cities incl. Amount and Rank
List of cities with rank (but not sorted by rank):
Figure 2 - Unsorted List - Output
2. Create a Sorted List
The sorted list is created with the following expression:
Figure 3 - Sorted List
The basic idea is to construct a ForEach loop based on GENERATE and use the first argument - the collection - to control the order in which the items are processed. For each pass of the loop, a certain row of the cities list is to be extracted from the source set and inserted into the result - the variable "set1_SortedCustomers".
- - GENERATE is the DAX equivalent of a ForEach loop
- - GENERATESERIES is used as the first argument for GENERATE and represents the collection which is iterated over
- - FILTER is used as the second argument for GENERATE and corresponds to the block that is run per each loop pass
Comparison between GENERATE and an OOP-based loop:
- GENERATE ( // ForEach (...)
- <set1>, // ForEach ( <item> in <collection> )
- <set2> ) // ForEach ( <item> in <collection> ) { <operation> }
In the example, an integer list is to be iterated over. The integer list is the counterpart of the rank values. For example, the list of cities contains 8 items, which are ranked from 1 to 8 respectively. Therefore, the first argument for GENERATE should be a list of integers from 1 to 8. This list is generated with GENERATESERIES.
Figure 4 - GENERATESERIES
Figure 5 - GENERATESERIES Output
Please notice that the output of GENERATESERIES contains a column named [Value]. This column will later be referenced.
The integer list should start at the constant value of 1. The upperbound should be set dynamically and correspond to the number of items in the list of cities. COUNTROWS is used for this.
Figure 6 - GENERATESERIES with Dynamic Upperbound
The second argument of GENERATE is also a set expression that is evaluated per loop. With the help of FILTER, all cities are extracted whose rank is identical to the value of the current loop counter value. Since the "DENSE" option leads to unique rank values, exactly one city is found per loop pass and inserted into the result.
Figure 7 - FILTER used as Loop-Block
3. Output the Sorted List as a Concatenated String
The output of a sorted list is not part of the solution approach, but is intended to illustrate that the sorting happens BEFORE the output:
Figure 8 - Concatenated String
Figure 9 - Concatenated String Output
4. Complete Code
Figure 10 - Complete Code