Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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 🙂
Solved! Go to Solution.
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.
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.
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!
It worked, thank you so much for the help! 🙂
Count is a measure?
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.
Ok, "Auto count" is a measure too (an implicit measure, not adviced, but ok) 🙂
Then i think it's not possible.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
21 | |
17 | |
16 | |
11 | |
7 |
User | Count |
---|---|
26 | |
25 | |
12 | |
12 | |
12 |