Mastering Multiple Outputs in Measures: The Magic of CONCATENATEX, UNICHAR(10), TOPN & SELECTCOLUMNS

[technolog]

Let's break down the task of outputting multiple values in a measure. To do this, the TOPN function should be wrapped in some other function that will return one column, for example through the SUMMARIZE function, which will output one column with unique values. And you can also use the SELECTCOLUMNS function, which selects one or more of the required columns and outputs them. In addition, in SELECTCOLUMNS you can specify the name of the column to be output.

Let's start by outputting one column with product IDs and answer the question: "Which product has the maximum sales amounts in an order?".

To see the result, let's sort our Orders table in descending order by the Total field

 

Now let's output the top five values in measure. We need to calculate them beforehand, and for this we wrap the TOPN function in the SELECTCOLUMNS function, in the first parameter of SELECTCOLUMNS we feed the TOPN function, and in the second parameter we write the name of the column to be created, for example, it will be top_products.

 

How to output these values via measure?

If we need to calculate top-3, top-5, top-100, etc., we can use the text-based iterative X function CONCATENATEX.

To use it in practice, we need to wrap the entire table consisting of one column and five rows in the CONCATENATEX function. In the first parameter of the function we specify the table, in the second parameter we specify the top_products column.

The CONCATENATEX function will combine the rows into one row.

 

The combined text line is separated by the appropriate separator. In order to use this information more conveniently, it is necessary to display sales for each product ID, and sort the output values in the appropriate order: the product with the highest sales is displayed first, and then in descending order.

First of all, let's display the value of sales sums to product IDs. Using the SELECTCOLLUMNS function we output not one column, but two, adding a new column to the code, let's call it total. Then we add the second column total to the CONCATENATEX function, and since CONCATENATEX returns text, the second column will also become text.

DAX has a text merge operator - it is &

 

 

It remains to set up sorting, so that the product with the highest sales is shown first, then with the lowest, etc.

The CONCATENATEX function can sort the values. It has two additional parameters. We add parameters to it: the column by which we want to sort the given expression, in our case it is the total column and the last fifth parameter of the CONCATENATEX function is to specify the sorting method, the DESC parameter is responsible for sorting from greater to lesser.

You can also use a line break as a delimiter. To do this, you can use the UNICHAR(10) expression