How to setup a slicer TOP10/Full display

[Pbiuserr]

Hello,

I am interested in creating a slicer for TOP10 of chart by measure or full distribution.

Something similar to https://www.youtube.com/watch?v=SsZseKOgrWQ but she does different kind of TOP N, and I really don't want to do value like 999 to be sure all of entries would be displayed

Anyone know how to setup such thing?

[Anonymous]

@Pbiuserr , I would suggest this:

Create a slicer that has the options "Top 10" and "All"

Create a measure that works out whether a row should be displayed or not based on the slicer selection. Something like

ShowRow =
    SWITCH(TRUE(),
        SELECTEDVALUE('Slicer'[Value]) = "All", "Y",
        SELECTEDVALUE('Slicer'[Value]) = "Top 10",
        IF({Your logic to work out if the row falls within the top 10} = TRUE(), "Y"),
        "N"
    )
    

Then in your table visual, add the measure [ShowRow] as a filter and select the value Y.

Alternatively, you could use the method in the YouTube video, but use this data for the TopN table:

TopNValue	TopN
10	Top 10
999999999	All

[Pbiuserr]

Hey,

I dont want to use 9999 because as model scales it can get troublesome

{Your logic to work out if the row falls within the top 10}

 I dont get fully this one. My measure is a count, so I need to prepare RANKX measure on that count and TOPN 10?

[Anonymous]

@Pbiuserr , "{Your logic to work out if the row falls within the top 10}" is just a placeholder. You will need to put in the logic that you want in here. I cannot put in the logic because you have not given enough details to enable me to do so.

Could you please post the DAX of your count measure? And what table and fields are you ranking by e.g. are you ranking customers, product, or ...?

[Pbiuserr]

Hi,

Measure is = CALCULATE(COUNTROWS(FactTable),
ATV_FLG = 1 )

I rank them by countries

[Anonymous]

@Pbiuserr , this is my suggested solution.

Create this measure:

CountryCountRank = 
    VAR vCountry = SELECTEDVALUE('FactTable'[Country])
    
    VAR vCountrowsMeasure = 
        CALCULATETABLE(
            ADDCOLUMNS(
                SUMMARIZE(
                    FactTable
                    ,FactTable[Country]
                )
                ,"_CountrowsMeasure", [YourCountrowsMeasure]
            )
            ,REMOVEFILTERS(FactTable[Country])
        )

    VAR vRank = 
        ADDCOLUMNS(
            vCountrowsMeasure
            ,"_rank", RANKX(vCountrowsMeasure, [_CountrowsMeasure])
        )
    RETURN
        MAXX(
            vRank
            ,IF(FactTable[Country] = vCountry, [_rank])
        )

then create another measure:

ShowRow = 
        SWITCH(TRUE(),
            ISBLANK([CountryCountRank]), BLANK(),
            NOT ISFILTERED('TopNSlicer') && NOT ISBLANK([CountryCountRank]), "Y",
            ISFILTERED('TopNSlicer') && [CountryCountRank] <= SELECTEDVALUE('TopNSlicer'[TopNValue]), "Y"
        )

set the Format of the [ShowRows] measure to Text.

Add the [ShowRows] measure as a filter onto the table visual where you are displaying the CountryResults, and set the the filter to equal Y.

You will need a table for the slicer called TopNSlicer with a field TopNValue. Populate it with a row with TopNValue 10 (and other if you wish).

When I select nothing in the slicer, I get all the countries like this:

When I select a value in the TopNSlicer, such as 4, I get the top 4 ranked countries:

[Pbiuserr]

Hi,

Thanks, and how you switch from to full display, considering there is single select on a slicer (requirement). Also a chart is made out of field parameter, so this one will work only on "country" entry?

[Anonymous]

@Pbiuserr , if your slicer has to be single-select, then put this data in your TopNSlicer table:

TopN	TopNValue
Top10	10
All	0

and change the [ShowRow] measure to this:

ShowRow = 
        SWITCH(TRUE(),
            ISBLANK([CountryCountRank]), BLANK(),
            NOT ISFILTERED('TopNSlicer') && NOT ISBLANK([CountryCountRank]), "Y",
            ISFILTERED('TopNSlicer') && SELECTEDVALUE('TopNSlicer'[TopN]) = "All", "Y",
            ISFILTERED('TopNSlicer') && [CountryCountRank] <= SELECTEDVALUE('TopNSlicer'[TopNValue]), "Y"
        )

Let me know when you get this working for Countries.