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.
Confused.
Here is my simple as data set.
Table name = complaints
Columns: Tier2, Cases
I just want to be able to filter the table by top5 cases by Tier2 category.
Any solutions?
Thanks
Solved! Go to Solution.
FilteredTable = TOPN( 5, complaints, complaints[cases], DESC )
Hi @johnmelbourne
You can use Rank as a measure like below.
Rank Cases by Tier2= RANKX( CALCULATETABLE( VALUES( complaints[Tier2] ), ALLSELECTED() ), CALCULATE( SUM( complaints[Cases] ) ),, DESC )
Or Column
Rank Cases by Tier2 = RANKX( VALUES( complaints[Tier2] ), CALCULATE( SUM( complaints[Cases] ) ),,, Dense )
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @johnmelbourne
You can use Rank as a measure like below.
Rank Cases by Tier2= RANKX( CALCULATETABLE( VALUES( complaints[Tier2] ), ALLSELECTED() ), CALCULATE( SUM( complaints[Cases] ) ),, DESC )
Or Column
Rank Cases by Tier2 = RANKX( VALUES( complaints[Tier2] ), CALCULATE( SUM( complaints[Cases] ) ),,, Dense )
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Mariusz
I thought the TOPN to filter the table would be easy, but I am still struggling.
Here is my visualisation table using your rankx formula (which works great!). How would I use a TopN to reduce this table to say a top 5, or even better, a dynamic N using a variable? / slider?
Thanks
John
Hi @johnmelbourne
Please see the below.
Top N Sales = VAR n = MAX( 'Top N Selection'[Select Top N] ) -- Unrelated Table with one column and values for top n selection, example (1, 5, 10, 15) VAR tbl = TOPN( n, ALLSELECTED( 'Product'[Brand] ), -- replace with complaints[Tier2] [Sales], --replace with CALCULATE( SUM( complaints[Cases] ) ) DESC ) RETURN CALCULATE( [Sales], --replace with SUM( complaints[Cases] ) KEEPFILTERS( tbl ) )
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you @Mariusz for the additional procedure. Love to learn. Your solutions were perfect. My TOPN approach kept on throwing an error saying multiple columns. Then you provioded your solution and I also read this article which explained why TOPN is useless by itself. https://www.dutchdatadude.com/power-bi-pro-tip-confusion-about-topn-versus-rankx/
Your contribution was perfect. Thanks @Mariusz
FilteredTable = TOPN( 5, complaints, complaints[cases], DESC )
This article helped, in that TOPN is useless by itself.
https://www.dutchdatadude.com/power-bi-pro-tip-confusion-about-topn-versus-rankx/
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 |
---|---|
107 | |
94 | |
77 | |
63 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |