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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.