Forum Discussion
Use one date slicer to control multiple visuals
Hi, I have a question on how to use one date slicer to control multiple visual on the same page. I have two tables, one for transaction (fact table) and one for product (dim table). On transaction table, it has two dates, production date and transaction date. On product table, it has one date, termination date, it indicates when this product was no longer produced.
I'd like to create a visual to see recent activities on what products have been produced, what products have been traded, and what products have been terminated. For example, when I selected last 90 days, it should show me three visuals 1. first visual contains a list of products have been produced, 2. second visual contains a list of products have been traded, and 3. third what products have been terminated.
I tried to create a calendar table and use one date slicer to control all three visuals, but I have some trouble of doing that. See sample dataset (transaction and product) below.
Any ideas of how I can achieve this goal?
Hi , oated
According to your description, you want to use one date slicer to control three visuals .
For your needs, you need to create a date table, you don't need to create a relationship with your two tables. Then you can use the date column of this date table as a slicer. You can write the measurement value according to your business logic, and then put it in the "Filter on this visual" corresponding to the visual.
Here are the steps you can refer to :
(1)First , we need to click "New Table " to create a calculated table:Date = CALENDAR( FIRSTDATE('fact table'[Production Date]) , LASTDATE('Dim Table'[Terminated Date]))(2)Then we need to create three measures.I'm not sure about your business logic here, so you can modify this measure according to your actual needs.products have been produced = var _select_min_date = MIN('Date'[Date])var _select_max_date = MAX('Date'[Date])var _cur_produced_date = MAX('fact table'[Production Date])returnIF( _cur_produced_date>= _select_min_date && _cur_produced_date<= _select_max_date ,1,0)products have been terminated = var _select_min_date = MIN('Date'[Date])var _select_max_date = MAX('Date'[Date])var _cur_terminated_date = MAX('Dim Table'[Terminated Date])returnIF( _cur_terminated_date>= _select_min_date && _cur_terminated_date<= _select_max_date ,1,0)products have been traded = var _select_min_date = MIN('Date'[Date])var _select_max_date = MAX('Date'[Date])var _cur_traded_date = MAX('fact table'[Trade Date])returnIF( _cur_traded_date>= _select_min_date && _cur_traded_date<= _select_max_date ,1,0)(3)Then we can place these three metrics in the "Filter on this visual" of the corresponding visual.
2 Replies
Hi , oated
According to your description, you want to use one date slicer to control three visuals .
For your needs, you need to create a date table, you don't need to create a relationship with your two tables. Then you can use the date column of this date table as a slicer. You can write the measurement value according to your business logic, and then put it in the "Filter on this visual" corresponding to the visual.
Here are the steps you can refer to :
(1)First , we need to click "New Table " to create a calculated table:Date = CALENDAR( FIRSTDATE('fact table'[Production Date]) , LASTDATE('Dim Table'[Terminated Date]))(2)Then we need to create three measures.I'm not sure about your business logic here, so you can modify this measure according to your actual needs.products have been produced = var _select_min_date = MIN('Date'[Date])var _select_max_date = MAX('Date'[Date])var _cur_produced_date = MAX('fact table'[Production Date])returnIF( _cur_produced_date>= _select_min_date && _cur_produced_date<= _select_max_date ,1,0)products have been terminated = var _select_min_date = MIN('Date'[Date])var _select_max_date = MAX('Date'[Date])var _cur_terminated_date = MAX('Dim Table'[Terminated Date])returnIF( _cur_terminated_date>= _select_min_date && _cur_terminated_date<= _select_max_date ,1,0)products have been traded = var _select_min_date = MIN('Date'[Date])var _select_max_date = MAX('Date'[Date])var _cur_traded_date = MAX('fact table'[Trade Date])returnIF( _cur_traded_date>= _select_min_date && _cur_traded_date<= _select_max_date ,1,0)(3)Then we can place these three metrics in the "Filter on this visual" of the corresponding visual.Hi v-yueyunzh-msft ! This solution is perfect! I have accepted this as a solution. Thanks for your help!
Just FYI I also figured out another potential solution which is using the Sync slicers function, this also works for my report.
