Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
J_Mug
Helper I
Helper I

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 5
v-gizhi-msft
Community Support
Community Support

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:

9.PNG

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:

7.PNG8.PNG

See my attached pbix file.

 

Best Regards,

Giotto

 

 

Greg_Deckler
Super User
Super User

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. 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Here is another example (below)



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
Super User

@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...

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Greg_Deckler
Super User
Super User

You should have an option in your Filter pane for TopN filtering for the column.

image.png



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.