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
kev_oactive
Regular Visitor

Dynamic Filter on page

Hi all, 

 

I have a table that has the following columns:

  1. Customer
  2. SalesManager
  3. State
  4. Date
  5. ShipmentCount
  6. PriceCheckCount

Where the Price Check Count is simply the amount of times a customer checks for a price but don't ship the product, and the Sales Manager is the employee responsable for that customer. 

 

So in the report I have a date range, and 3 tables. The first table is aggregating all the counts at the SalesManager level, the second one at the Customer level and the last one at the State level (just in case, these 3 tables are just visuals showing data at different levels, but the data in the model is comming from one table as I mentioned above). 

 

What I want to show in this report is the data for all the customers that, in the selected time range, the PriceCheckCount > ShipmentCount. This condition cannot be resolved by adding a column in the table cause it needs to change dynamically based on the date range. So I created a measure called deviation to show the difference between ShipmentCount and PriceCheckCount, and I'm filtering the second table (Customer level) where that measure is negative. 

 

Now I need to filter the other 2 tables based on the set of customers that I have as a result in the second table. I cannot use the same measure here because that will calculate the difference at the Employee and State level, and that result is not the same as filtering Employee and State counts based the set of customers where the total amount of PriceCheckCount > ShipmentCount in the selected time range. 

 

Does anybody have an idea of how I can achieve that? 

Thanks a lot. 

1 REPLY 1
Anonymous
Not applicable

Sample data in textual form, please. Also, it would be advisable to attach a desired output from a known set of inputs.

Regardless of the above, having one big table in the model is a bad design practice. Please refer to these for correct modeling techniques:

https://www.youtube.com/watch?v=_quTwyvDfG0

https://www.youtube.com/watch?v=78d6mwR8GtA

Best
D

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.

Top Solution Authors