Forum Discussion
Helper ISlicing 2 Dates with One Slicer without using a common Date Table
I'm having a little trouble with how to approach setting up a Report that uses one filter to slice two dates. Typically the approach is to link with a common Date Table. However, here the two tables are already related.
There are two tables: Work Orders and Time Confirmations. Work Orders are the parent table to Confirmations:
What certain customers want is to be able to see a last week summary where they can see both Orders completed last week and hours confirmed last week. In other words Last Week must apply to [Confirmations].[Confirmation Date] and [Work Orders].[TECO Date].
How can I accomplish filtering these two dates with one slicer?
Hi, jribs
According to your description, I can roughly understand your requirement, I think you can achieve this using the measure and visual filter, you can try my steps:
- Go to the relationship view and make sure the calendar table doesn’t have a relationship with any of the two tables:
- Create two measures within the two tables separately:
Flag1 = var _selectedperiod=SELECTCOLUMNS(ALLSELECTED('Calendar'),"1",[Date]) return IF(MAX([TECO Date]) in _selectedperiod,1,0)Flag2 = var _selectedperiod=SELECTCOLUMNS(ALLSELECTED('Calendar'),"1",[Date]) return IF(MAX([Confirmation Date]) in _selectedperiod,1,0)- Create two table charts for the two tables and apply the visual filter for the two table charts like this:
And you can get what you want, like this:
You can download my test pbix file below
If you still have a problem, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- v-robertq-msft
Community Support
Hi, jribs
According to your description, I can roughly understand your requirement, I think you can achieve this using the measure and visual filter, you can try my steps:
- Go to the relationship view and make sure the calendar table doesn’t have a relationship with any of the two tables:
- Create two measures within the two tables separately:
Flag1 = var _selectedperiod=SELECTCOLUMNS(ALLSELECTED('Calendar'),"1",[Date]) return IF(MAX([TECO Date]) in _selectedperiod,1,0)Flag2 = var _selectedperiod=SELECTCOLUMNS(ALLSELECTED('Calendar'),"1",[Date]) return IF(MAX([Confirmation Date]) in _selectedperiod,1,0)- Create two table charts for the two tables and apply the visual filter for the two table charts like this:
And you can get what you want, like this:
You can download my test pbix file below
If you still have a problem, you can post some sample data(without sensitive data) and your expected result.
How to Get Your Question Answered Quickly
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- amitchandak
Super User
jribs , use independent date and create measure on both tables using that
Confirmations1 =
var _max = maxx(allselected('Date'), Date[Date])
var _min = minx(allselected('Date'), Date[Date])
return
calculate(countrows(Confirmations), filter(Confirmations, Confirmations[Confirmations] >=_min && Confirmations[Confirmations Date]<=_max))
Work Orders 1 =
var _max = maxx(allselected('Date'), Date[Date])
var _min = minx(allselected('Date'), Date[Date])
return
calculate(countrows('Work Orders'), filter('Work Orders', 'Work Orders'[TECO Date] >=_min && 'Work Orders'[TECO Date]<=_max))refer
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
- jribs
Although this works for filtering 2 measures from 2 separate dates, It only works for the measures themselves. It does not filter lists simulataneously. See the solution implemented, with the filter changed, only the two measures change and not the list:
