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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors