Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
natg
Frequent Visitor

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 accomplished)

 

Visual 2:  Where my Top N customers are from (Preferably on a map, to plot only data points of customers who are in First visual)

 

Visual 3: What products are bought by my Top N customers? (Any visual that can restrict the data based on first visual)

 

I have uploaded the PBIX file in this lication. 

https://drive.google.com/open?id=17G4GEJzPWcjOMh9Zy-AME0ugSQF0ZXEZ

 

 

PowerBI _Dashboard.png

 

 

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

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
)

r1.PNG

 

Here is the modified pbix file for your reference. Smiley Happy

 

Regards

View solution in original post

2 REPLIES 2
v-ljerr-msft
Employee
Employee

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
)

r1.PNG

 

Here is the modified pbix file for your reference. Smiley Happy

 

Regards

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
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.