Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 @blinabj ,
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 @blinabj ,
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |