Forum Discussion

Martin_D's avatar
Martin_D
Icon for Solution Sage rankSolution Sage
2 years ago
Solved

Sorting a single column table within a measure?

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

  • aduguid's avatar
    aduguid
    2 years ago

    Give this a try 

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

2 Replies

  • aduguid's avatar
    aduguid
    Icon for Memorable Member rankMemorable Member
    2 years ago

    Give this a try 

    CONCATENATEX (
    SELECTCOLUMNS (
        TOPN (
            5,
            DISTINCT ( 'Customer'[City] ),
            'Customer'[City], ASC
        ),
        "City", 'Customer'[City]
    ),
    [City],
    ", ",
    'Customer'[City], ASC
)
  • Martin_D's avatar
    Martin_D
    Icon for Solution Sage rankSolution Sage
    2 years ago

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