Limit items in visual (TOPN?)

[J_Mug]

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,

[v-gizhi-msft]

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

[Greg_Deckler]

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. 

[Greg_Deckler]

Here is another example (below)

[amitchandak]

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

[Greg_Deckler]

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