topic Re: Dynamic table sorting based on total count with fixed value at the bottom in DAX Commands and Tips

Dynamic table sorting based on total count with fixed value at the bottom

Anonymous — Tue, 01 Nov 2022 15:32:57 GMT

Hi,

I want to sort this table dynamically. So that the color category with the highest count is at the top, and the lowest at the bottom, but, I want "Unknown", to always be the last value in the table even though it has a high count. How do I do this?

Thanks in advance, highly appreciate it 🙂

Re: Dynamic table sorting based on total count with fixed value at the bottom

lukiz84 — Tue, 01 Nov 2022 15:41:05 GMT

Count is a measure?

Re: Dynamic table sorting based on total count with fixed value at the bottom

Anonymous — Tue, 01 Nov 2022 15:51:46 GMT

No, it just an automatic count done by the table of the IDs for each category. Here is the table the data is taken from. So, no measure is used so far.

Re: Dynamic table sorting based on total count with fixed value at the bottom

lukiz84 — Tue, 01 Nov 2022 16:10:54 GMT

Ok, "Auto count" is a measure too (an implicit measure, not adviced, but ok) 🙂

Then i think it's not possible.

Re: Dynamic table sorting based on total count with fixed value at the bottom

v-yadongf-msft — Thu, 03 Nov 2022 08:25:45 GMT

Hi Anonymous ,

This is my test table:

Please create two columns:

Count = IF('Table'[ID] = MINX(FILTER('Table','Table'[Category] = EARLIER('Table'[Category])),'Table'[ID]), CALCULATE(COUNT('Table'[Category]),FILTER('Table','Table'[Category] = EARLIER('Table'[Category])))) Rank = SWITCH( TRUE(), 'Table'[Category] = "Unknown" && 'Table'[Count] <> BLANK(),DISTINCTCOUNT('Table'[Category]), 'Table'[Count] <> BLANK(),RANKX('Table',[Count],,DESC,Dense))

You can sort by [Rank]:

If you don't want to see Rank column in table visual, you can hide it in Specific column:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Dynamic table sorting based on total count with fixed value at the bottom

Anonymous — Thu, 03 Nov 2022 11:26:55 GMT

It worked, thank you so much for the help! 🙂

Re: Dynamic table sorting based on total count with fixed value at the bottom

Anonymous — Mon, 12 Dec 2022 21:11:35 GMT

Hi again v-yadongf-msft,

I have now got 0 values as well (For example for Color: Pink, the Sum of Count = 0). How can I make it work with that?

Thanks in advance, highly appreciate it!