How to sort a TopN DAX function in measure for PowerBI

Question

I'm struggling with an issue where I'm trying to use TopN in a measure but the order specified (asc/desc) doesn’t turn out right. Example:


CONCATENATEX ( 

topn( 5, 

    all( 'Table'[Column] ), 

    calculate( COUNT( 'Table'[Column]) ),

    desc

),

'Table'[Column],

   unichar(10)

)

The measure outputs the right counted col values (5 most frequently occurring) but in a backwards order (asc vs desc) and it wont let me fix it even though I’ve tried a bunch of different methods including RANKX.

Similar questions:

https://community.powerbi.com/t5/Desktop/I-want-to-quot-Order-by-quot-before-using-CONCATENATEX-tried/td-p/136008

https://community.powerbi.com/t5/DAX-Commands-and-Tips/DAX-Measure-Sort-by-issue/td-p/1116147

ASC parameter when using TOPN function in Power BI

SqueakyBeak SqueakyBeak · Accepted Answer · 2020-11-03T17:42:40

"The order parameter of the TOPN part is just about the ordering of which part to select for to iterate for CONCATENATEX - not about the order of the iterator. There are optional arguments for CONCATENATEX to decide in which order it should iterator the table. You can use these like this:"

CONCATENATEX (
    ...
    UNICHAR(10),
    **'Table'[Column],
    DESC**
)

How to sort a TopN DAX function in measure for PowerBI

1 Answers