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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
imnikki
Frequent Visitor

I want to "Order by" before using CONCATENATEX ... tried TOPN and it doesn't work

Has anyone figured out how to sort records before concatenating them using CONCATENATEX? 

 

I tried to sort by descending date using TOPN but it doesn't display sorted.

 

 Test Sorting Results.png

  

 Test Sorting =
 CONCATENATEX(
  TOPN(100,'x', YEAR('x'[Date]) & "-" &
   REPT("0",2-LEN(MONTH('x'[Date]))) & MONTH('x'[Date]) & "-"&
   REPT("0",2-LEN(DAY('x'[Date]))) & DAY('x'[Date]), DESC),
  YEAR('x'[Date]) & "-" &
   REPT("0",2-LEN(MONTH('x'[Date]))) & MONTH('x'[Date]) & "-"&
   REPT("0",2-LEN(DAY('x'[Date]))) & DAY('x'[Date]) & " " & 'x'[Status Name],
 " | ")

 

Can I use EVALUATE ... ORDER BY ... somehow in Power BI?

 

Thanks.

1 ACCEPTED SOLUTION
bblais
Resolver III
Resolver III

Hi @imnikki,

 

The syntax of the CONCATENATEX function within Power BI is a little different than what you see in the MSDN documentation, so it fooled me as well, but I figured it out.  You are correct that the suggestion of sorting your data first within the query has no effect on the results. Instead, you need to enter the "order by expression" as parameter 4 before entering ASC/DESC in parameter5.

 

CONCATENATEX(Table, Expression, [Delimiter], [OrderBy_Expression1], [Order1], ...)

 

So in your example, try repeating your Expression again after the delimiter, then use DESC as the 5th parameter.

 

Let me know if that works.

 

Thanks,

Bill

View solution in original post

9 REPLIES 9
bblais
Resolver III
Resolver III

Also, I think you are going about formatting your date all wrong.  You can easily use the FORMAT function like this to get the format you want:

 

FORMAT([Date],"yyyy-MM-dd")

 

And then really I think you just want to sort by [Date], so use just [Date] as your 4th parameter rather than the entire expression where you are concatenating date and status name, something like this...

 

 

CONCATENATEX('x', FORMAT('x'[Date],"yyyy-MM-dd") & " " & 'x'[Status Name],"|",'x'[Date],DESC)

 

Anonymous
Not applicable

thanks so much for this @bblais 

Just as a final follow up, if you want UNIQUE values in your concatenated list you would use something like this:

 

First create calculated column on table 'x'

 

[Date-Status] = FORMAT('x'[Date],"yyyy-MM-dd") & " " & 'x'[Status Name]

 

Then create this measure with a CONCATENATEX call with a slight difference, using VALUES instead of just the table.  The trick is your using the CALCULATE function AND using the VALUES() function to get the distinct values of that column to be concatenated:

 

[All Date-Status] = CALCULATE(CONCATENATEX(VALUES('x'[Date-Status]), 'x'[Date-Status], "|", [Date-Status], DESC)

 

kevhav
Continued Contributor
Continued Contributor

bblais is right -- I also just figured out the same thing, after some trial and error, and despite the incomplete documentation of the CONCATENATEX function.

 

Although in my case, it was with a simpler call to CONCATENATEX. I can't guess whether it'll work the same when you have all of that complex stuff within your CONCATENATEX.

bblais
Resolver III
Resolver III

Hi @imnikki,

 

The syntax of the CONCATENATEX function within Power BI is a little different than what you see in the MSDN documentation, so it fooled me as well, but I figured it out.  You are correct that the suggestion of sorting your data first within the query has no effect on the results. Instead, you need to enter the "order by expression" as parameter 4 before entering ASC/DESC in parameter5.

 

CONCATENATEX(Table, Expression, [Delimiter], [OrderBy_Expression1], [Order1], ...)

 

So in your example, try repeating your Expression again after the delimiter, then use DESC as the 5th parameter.

 

Let me know if that works.

 

Thanks,

Bill

This is awesome.  Thank you Bill (@bblais) for the solution and recommendation!  My users will be happy!  I added UNICHAR(10) and the tooltip shows the details on separate line. 

 

Detail String = IF(NOT(ISBLANK('x'[ID])), FORMAT('x'[Date], "yyyy-MM-dd")  & " " & 'x'[Status Name] , "")

Details = CONCATENATEX('x','x'[Detail String], UNICHAR(10), 'x'[Detail String], DESC)

 

UNICHAR(10) preserves the line feed in Excel when you export the data.  UNICHAR(13) does not.

You are welcome @imnikki.  That's a great addition of using the LF character to separate each item onto its own line as well, much more readable in tooltips.

v-caliao-msft
Microsoft Employee
Microsoft Employee

Hi @imnikki,

 

Currently, EVALUATE function cannot be used when creating calculated measure or calculated column. In your scenario, you could sort your data first and then use CONVATENATEX function.
queryoverview_queryview.png

Capture.PNG

 

Regards,

Charlie Liao

Thanks Charlie @v-caliao-msftfor the great suggestion. I did give it a try, sorting in "Edit Query", but unfortunately it did not preserve the sort during concatenation in DAX.  We would need to have an extra sort order parameter for the CONCATENATIONX function.

 

Thanks again.

Nicole

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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