Forum Discussion

natg's avatar
natg
Frequent Visitor
8 years ago
Solved

Dynamic filter across visuals

Pros,   I am trying to build a dashboard with a set of visuals where the dashboard need to answer the following questions.   Visual 1: Who are my Top 5/10 customers by a measure (THis is accompli...
Need Help
Show and Tell
Tips and Tricks
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi natg,

     

    Based on my test, you can firstly use the formula below to create the measure to calculate the [Custom Rank], then you should be able to use the [Final Sales] measure to apply visual level filters([Final Sales] is greater than 0) on the visuals of the three answers to get the expected result in your scenario.

    Customer Rank = 
RANKX (
    ALL ( 'Sample'[Customer] ),
    CALCULATE ( [Total Sales], ALLEXCEPT ( 'Sample', 'Sample'[Customer] ) ),
    ,
    DESC
)

     

    Here is the modified pbix file for your reference. :smileyhappy:

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

Hi natg,

 

Based on my test, you can firstly use the formula below to create the measure to calculate the [Custom Rank], then you should be able to use the [Final Sales] measure to apply visual level filters([Final Sales] is greater than 0) on the visuals of the three answers to get the expected result in your scenario.

Customer Rank = 
RANKX (
    ALL ( 'Sample'[Customer] ),
    CALCULATE ( [Total Sales], ALLEXCEPT ( 'Sample', 'Sample'[Customer] ) ),
    ,
    DESC
)

 

Here is the modified pbix file for your reference. :smileyhappy:

 

Regards

  • natg's avatar
    natg
    Frequent Visitor
    8 years ago

    Hi v-ljerr-msft,

     

    Thanks for the help. Looks like need to spend more time understanding how the all except works in this case.

     

    When I applied the product filter, the visuals are fine and working as expected. Whereas the rank index values were incorrect in the table. 

     

    Once I included Product also as another clause in the all except option, it looked fine. Many thanks for the help.

     

    Customer Rank =
    RANKX (
    ALL ( 'Sample'[Customer] ),
    CALCULATE ( [Total Sales], ALLEXCEPT ( 'Sample', 'Sample'[Customer], 'Sample'[Product] ) ),
    ,
    DESC
    )