Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

order by based on first column in concatenation ssas dax

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

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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 . 

View solution in original post

1 REPLY 1
HotChilli
Super User
Super User

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 . 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors