topic Re: Top N slicer using measure in DAX Commands and Tips

Top N slicer using measure

TSki — Mon, 17 Oct 2022 23:03:46 GMT

I am trying to make a sliding slicer off of this measure. I found this code from another user that, gives the TOPN for using COUNTROW to get the TOPN of the most repeated values.

Measure =
VAR __topn =
    TOPN (
        5,
        VALUES ( Table[Category Level 1] ),
        RANKX ( ALL ( Table[Category Level 1] ), COUNTROWS ( Table ),, ASC )
    )
RETURN
    CALCULATE ( COUNTROWS ( Table ), __topn )

My is slicer is called "TOPN" and it can be slid from 0-20, where the number it is on should be the number of top values shown.

Re: Top N slicer using measure

v-yadongf-msft — Tue, 18 Oct 2022 02:07:58 GMT

Hi TSki ,

Do you want to count the number of the most most repeated values and then use it as slicer? If so, please try following steps.

This is my test table:

Create a new column instead of a measure:

The most repeated values = CALCULATE(COUNT('Table'[Category Level 1]),FILTER('Table','Table'[Category Level 1] = EARLIER('Table'[Category Level 1])))

Create a slicer using the column just created:

If I misunderstand your demands, please feel free to contact us in time.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Top N slicer using measure

TSki — Tue, 18 Oct 2022 13:35:31 GMT

Thanks for your reply v-yadongf-msft ,

With this new column, can I apply a TOPN measure to it and then apply to a slicer? Where a one on the slicer displays the most repeated value, 2 shows the top 2 most repeated values, and so on. So in your test data, when the slicer is slid to 1: B will be displayed and when slid 2: B and D are displayed, when on 3: B, D, E, A are displayed and on 4: B, D, E, A, C are displayed.

Re: Top N slicer using measure

v-yadongf-msft — Mon, 24 Oct 2022 03:04:55 GMT

Hi TSki ,

Unfortunately, you can't add measure to slicer. As an alternative, you can create a new column:

TOPN = RANKX('Table',[The most repeated values],,DESC,Dense)

I think this is pretty close to what you want:

Or like this:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.