Forum Discussion
Limit items in visual (TOPN?)
Hi,
I want to know if it's possible to limit the number of items in a visual to 10 or so, giving Power BI some conditions.
For example, I have 300 results por stores, for each store I know the NPS value and the margin of error for that value. So I want to see the first 10 stores in the bottom, when the NPS is below 50 and the margin of error is below 5%. This applies also to the Top, I want to see the first 10 stores that pass the condition of above 60 NPS and below 5% error.
I tried to use TOPN bottom, but it wont work as I'd have to use bottom 100 and doing that I had like 50 stores (the bottom 10 by margin of error or NPS returns a blank as the true bottom by NPS or Margin of error does not meet the condition).
I think TOPN doesnt work as it returns the true top, and I don't want the top, I want to limit the view to the 10 first that meet that condition.
Kind regards,
5 Replies
You should have an option in your Filter pane for TopN filtering for the column.
J_Mug , You can create a Rank and use it other filters in filter clause or Visual level filter.
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finaleReading this a little closer, I think perhaps you need what I call a Complex Selector. So, you create a measure that essentially returns 1 when the conditions you specify are met and a 0 when they are not and then you use this measure to filter your visual. The concept is based on the Inverse Selector. https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Selector/m-p/648290#M325
I have attached an example and will attach another one.
Here is another example (below)
Hi,
I create a sample to show first 5 stores for Top and Buttom defined in your post.
Please take following steps:
1)Create a slicer table by Enter Data:
2) Try this measure:
Measure = VAR _buttom = IF ( MAX ( 'Table'[NPS] ) < 50 && MAX ( 'Table'[Error] ) < 0.05, RANKX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[NPS] < 50 && 'Table'[Error] < 0.05 ), CALCULATE ( SUM ( 'Table'[NPS] ) ), , ASC, DENSE ) ) VAR _top = IF ( MAX ( 'Table'[NPS] ) > 60 && MAX ( 'Table'[Error] ) < 0.05, RANKX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[NPS] > 60 && 'Table'[Error] < 0.05 ), CALCULATE ( SUM ( 'Table'[NPS] ) ), , ASC, DENSE ) ) RETURN SWITCH ( SELECTEDVALUE ( SlicerTable[Slicer] ), "Top", IF ( _top <= 5 && _top <> BLANK (), 1 ), "Buttom", IF ( _buttom <= 5 && _buttom <> BLANK (), 1 ) )3)When select one value in slicer, it shows:
See my attached pbix file.
Best Regards,
Giotto
