Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Top N using multiple slicers

Hi,   I need to rank performance of products using multiple slicers. For example, selecting top N products selling well in October 2020. The slicers here are Date and Top N.   Please could someon...
Need Help
v-xicai's avatar
v-xicai
Community Support
6 years ago

Hi Anonymous ,

 

You may create new calculated table, rename the result column with "TopN", use the column  'TopN'[TopN] as the source of slicer.

 

TopN= GENERATESERIES(0, 10, 1)

 

Then you may create measure like DAX below.

 

TopN Value =

VAR _SelectedTop =

    SELECTEDVALUE ( 'TopN'[TopN] )

RETURN

    SWITCH (

        TRUE (),

        SelectedTop = 0, [Value],

        RANKX ( ALLSELECTED ( Table1 ), [Value] ) <= _SelectedTop, [Value]

    )

 

Best Regards,

Amy 

 

Community Support Team _ Amy

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi v-xicai ,

 

I couldn't call [Value] after selectedTop= 0. Is it because [Value] should be a measure rather than a column?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi v-xicai  ,

    Please could you have a look? I am not able to call [Value] in the formula.

    Thanks!

    • v-xicai's avatar
      v-xicai
      Community Support
      6 years ago

      Hi Anonymous ,

       

      If the [Value] is a column, you may change the formula like DAX below.

       

       

      TopN Value =
VAR _SelectedTop =
    SELECTEDVALUE ( 'TopN'[TopN] )
RETURN
    SWITCH (
        TRUE (),
        SelectedTop = 0, SUM ( [Value] ),
        RANKX ( ALLSELECTED ( Table1 ), [Value] ) <= _SelectedTop, SUM ( [Value] )
    )

       

       

      Best Regards,

      Amy 

       

      Community Support Team _ Amy

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