topic Re: Sorting a single column table within a measure? in DAX Commands and Tips

Sorting a single column table within a measure?

Martin_D — Thu, 27 Jun 2024 10:55:40 GMT

Hi,

Is there a way to sort a table within a measure?

The case:

I have a column 'Customer'[City] (could be any text column).

I want to CONCTENATEX the first five city names in the context, based on alphabetical order.

Using
CONCATENATEX ( TOPN ( 5, DISTINCT ( 'Customer'[City] ), [City], ASC ), [City], ", " )
works fine in that way that it always selects the correct cities, but the sorting is not applied to the output table of TOPN, it's just applied as a selection criterion. Since I want to concatenate the cities in alphabetical order, is there a way to express this order in a DAX measure?

Thank you very much!

Kind regards,

Martin

Re: Sorting a single column table within a measure?

aduguid — Thu, 27 Jun 2024 11:15:04 GMT

Give this a try

CONCATENATEX ( SELECTCOLUMNS ( TOPN ( 5, DISTINCT ( 'Customer'[City] ), 'Customer'[City], ASC ), "City", 'Customer'[City] ), [City], ", ", 'Customer'[City], ASC )

Re: Sorting a single column table within a measure?

Martin_D — Thu, 27 Jun 2024 11:22:29 GMT

Oh yeah, CONCATENATEX comes with sorting parameters! Thank you, man. This is not a generic sorting function, but it solves the problem 👍