cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jribs
Helper I
Helper I

Slicing 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:

jribs_0-1621345325961.png

 

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?

 

 

1 ACCEPTED SOLUTION
v-robertq-msft
Community Support
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:

  1. Go to the relationship view and make sure the calendar table doesn’t have a relationship with any of the two tables:

v-robertq-msft_0-1621500317977.png

 

  1. 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)

 

  1. Create two table charts for the two tables and apply the visual filter for the two table charts like this:

v-robertq-msft_1-1621500317985.png

 

And you can get what you want, like this:

v-robertq-msft_2-1621500317988.png

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.

View solution in original post

3 REPLIES 3
v-robertq-msft
Community Support
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:

  1. Go to the relationship view and make sure the calendar table doesn’t have a relationship with any of the two tables:

v-robertq-msft_0-1621500317977.png

 

  1. 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)

 

  1. Create two table charts for the two tables and apply the visual filter for the two table charts like this:

v-robertq-msft_1-1621500317985.png

 

And you can get what you want, like this:

v-robertq-msft_2-1621500317988.png

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



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

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:

 

jribs_0-1621358093202.png

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors