order by based on first column in concatenation ssas dax

[Anonymous]

HI Team,

Need order by first column in the concatenation query.

Ref Link: https://dax.do/3B3JEwkIK0m0P6/LZog4n/

I need result sort by first column after concatenation. below example : Unit is the first column here based on first column it need to be sorted.

Example:

"ConcatenateColumn",
IF (ISBLANK ([unit]),"0",[unit]) & "|" & -- column 1
IF (ISBLANK ('Product'[Brand]),"0",'Product'[Brand]) & "|" & --column 2
IF (ISBLANK (Customer[Occupation]),"0",Customer[Occupation]) --column 3

Query using but not working

define
var Req_columns=
ADDCOLUMNS (
SUMMARIZECOLUMNS (
'Product'[Brand],Customer[Occupation],"unit",CALCULATE (SUM (Sales[Unit Price]))/85*20
),

"ConcatenateColumn",
IF (ISBLANK ([unit]),"0",[unit]) & "|" &  -- column 1 based on this sorting 
IF (ISBLANK ('Product'[Brand]),"0",'Product'[Brand]) & "|" &
IF (ISBLANK (Customer[Occupation]),"0",Customer[Occupation])

)
evaluate Req_columns
ORDER by [ConcatenateColumn] asc

Query working sort by  Product Brand

define
var Req_columns=
ADDCOLUMNS (
SUMMARIZECOLUMNS (
'Product'[Brand],Customer[Occupation],"unit",CALCULATE (SUM (Sales[Unit Price]))/85*20
),

"ConcatenateColumn",
IF (ISBLANK ('Product'[Brand]),"0",'Product'[Brand]) & "|" &  -- Column 1 based on this sorting
IF (ISBLANK ([unit]),"0",[unit]) & "|" &
IF (ISBLANK (Customer[Occupation]),"0",Customer[Occupation])

)
evaluate Req_columns
ORDER by [ConcatenateColumn] asc

Thanks in Advance

[HotChilli · Accepted solution]

The concatenated column is a text field so the sorting is working correctly even though it's not what you want. In powerbi, you could use 'Sort by column' to sort by the 'unit' column but I think you are after an analysis services solution.  As long as 'unit' is a number, you can sort by that field in the 'evaluate' dax .