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

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

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

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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