Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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,
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
Reading 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)
@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-finale
Check this : https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
You should have an option in your Filter pane for TopN filtering for the column.
User | Count |
---|---|
120 | |
67 | |
66 | |
57 | |
50 |
User | Count |
---|---|
176 | |
83 | |
69 | |
65 | |
54 |