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

[imnikki]

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 =
 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.

[bblais]

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]

thanks so much for this @bblais 

[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]

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 · Accepted solution]

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

[imnikki]

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.

[bblais]

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]

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.

Regards,

Charlie Liao

[imnikki]

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