Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Top 10 only Slicer

Hello

 

I have my sales by vendor ranked, see below example, but I need to create a slicer to select the top 10 and top 20 only.  I have a TopN separate table (TopN), but dont know how to link them together so that my chart only shows the top 10 vendors when selected.

 

 

Invoice Datebase:

 

Vendor            Sales          Rank

a                       20                2

b                       10                3

c                        50               1

 

Any help would be brilliant.  Thanks

 

Gavin

Need Help
  • srinivt's avatar
    srinivt
    8 years ago

    Calculated columns will make this static and may not work as desired for certain use-cases where you want this to work when user slices/dices the data.

    You can write a DAX calculation using RANK measure which you already seem to have. 

     

    VAR rank = Table[RankMeasure]

     

    VAR rankFilter = 

    SWITCH (

          TopN[Filter],

          "Top 10",

          10,

          "Top 20",

          20,

          BLANK()

    )

     

    RETURN IF ( rank < rankFilter, rank, BLANK() )

     

    The above measure can be simplified further if the TopN[Filter] column is a numeric column with values 10, 20 etc. Then your measure can simply be written as : 

     

    VAR rank = Table[RankMeasure]

    RETURN IF ( rank < SELECTEDVALUE(TopN[Filter]), rank, BLANK() )

     

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    I do not believe that you can do a visual filter on a native Slicer. There might be a Slicer in the Store that does this. Otherwise, perhaps a Page level or Report level filter?

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      Anonymous

       

      How about adding a calculated column and slicing with it

       

      Top N Category =
IF (
    TableName[Rank] <= 10,
    "Top 10",
    IF ( TableName[Rank] <= 20, "Top 11-20", "Outside Top 20" )
)
      • srinivt's avatar
        srinivt
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Calculated columns will make this static and may not work as desired for certain use-cases where you want this to work when user slices/dices the data.

        You can write a DAX calculation using RANK measure which you already seem to have. 

         

        VAR rank = Table[RankMeasure]

         

        VAR rankFilter = 

        SWITCH (

              TopN[Filter],

              "Top 10",

              10,

              "Top 20",

              20,

              BLANK()

        )

         

        RETURN IF ( rank < rankFilter, rank, BLANK() )

         

        The above measure can be simplified further if the TopN[Filter] column is a numeric column with values 10, 20 etc. Then your measure can simply be written as : 

         

        VAR rank = Table[RankMeasure]

        RETURN IF ( rank < SELECTEDVALUE(TopN[Filter]), rank, BLANK() )