Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |