Forum Discussion

Maikeru's avatar
Maikeru
Helper II
8 years ago
Solved

TOP N elements using Visual filter on measure

Dear team,

 

I am trying to create a Top/Bottom N list of sales by customers for which [Sales Total] are between a dynamic range of values chosen by the user.
For this I would like to use the in-built Visual filter on measure in the Filter panel.


To achieve this I have created Rank measure which will dynamically switch from Descending to Ascending ranking order depending on the value of SLICER_RankingOrder (Top or Bottom).

 

Rank Customer = 
IF (NOT(ISBLANK([Sales Total])),
    SWITCH ( SELECTEDVALUE(SLICER_RankingOrder[SLICER_RankingOrder],"No value"),
        "Top", RANKX(ALLSELECTED(FACT_Sales[Customer]), [Sales Total], ,DESC), 
        "Bottom", RANKX(ALLSELECTED(FACT_Sales[Customer]), [Sales Total], ,ASC),
        RANKX(ALL(FACT_Sales[Customer]), [Sales Total], ,DESC)),
        BLANK()
)

 

 

And from there another measure Sales Top N to blank out any [Sales Total] which are beyond the N rank.

Sales Top N = 
IF ([Rank Customer] <= PARAM_RankingElements[RankingElements Value], [Sales Total], BLANK() )

So far so good.

 

Now when I apply the Visual filter on Total Sales measure, it seems to be applied on top of the ALLSELECTED customer context.

As a result I will get something like this (red). While the result I am looking for is in blue.

 

Is there a way to work around this?

 

 

 

Note: I found an alternate way using What-if parameters and integrating them in a calculated measure which I will then rank.
The result is fine but in terms of performance when I run this on my production dataset of 1M+ rows, it takes each time 5-10s to run. That's why I am exploring Visual filter on measure which seems to be giving much better performance results.

 

 

 

Thanks in advance for your help.

 

 

  • Hi Maikeru,

     

    I thought hard and found the measure doesn't change the context but the values. So I would suggest you add a calculated column to the table 'FACT_Sales'. Then it would be easy now.

    TotalSales =
    CALCULATE (
        SUM ( FACT_Sales[Sales] ),
        ALLEXCEPT ( FACT_Sales, FACT_Sales[Customer] )
    )
    

    TOP_N_elements_using_Visual_filter_on_measure

     

    Best Regards,

    Dale

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee

    Hi Maikeru,

     

    I thought hard and found the measure doesn't change the context but the values. So I would suggest you add a calculated column to the table 'FACT_Sales'. Then it would be easy now.

    TotalSales =
    CALCULATE (
        SUM ( FACT_Sales[Sales] ),
        ALLEXCEPT ( FACT_Sales, FACT_Sales[Customer] )
    )
    

    TOP_N_elements_using_Visual_filter_on_measure

     

    Best Regards,

    Dale

    • Maikeru's avatar
      Maikeru
      Helper II

      Thank you Dale! It works like a charm!