Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
96 | |
90 | |
79 | |
67 |
User | Count |
---|---|
150 | |
118 | |
111 | |
106 | |
95 |