cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kmpbi
Frequent Visitor

How to filter dates in one table based on selections from other

Hi PowerBi Community,

 

I have a many tables and relationships, but to summarize my problem - 

Table A

ObjectDateCt
A1-Jan5
A3-Jan10
A5-Jan15
B2-Jan1
B5-Jan2
B7-Jan3

 

Table B

ObjectDateStatus
A1-Jan1
A2-Jan1
A3-Jan0
A4-Jan0
A5-Jan1
B2-Jan1
B3-Jan1
B4-Jan1
B5-Jan1
B6-Jan0
B7-Jan0

the dates here will always

- be on a daily level

- have the start and end date according to the min and max date in table A according to each object

Table A and B have a relationship based on object column -

kmpbi_0-1632229790461.png

 

 

I have two visualizations, for table A and B, and the goal for me is to ensure both display for the same timeperiod.

 

kmpbi_2-1632195638889.png

NOTE: there are a few other slicers on the visual as well, which I would like to keep as is.

 

I have tried a few different approaches but none of them have worked out for me 😞 

 

- I have tried to add an intermediate date dimension table. This filters the both visuals on the correct dates, but then I am not able to pull the count of status for the correct objects.

- I have tried to use SELECTEDVALUES, VALUES, ALLEXCEPT but none of them have yielded correct results for me.

 

Any help with this is much appreciated! Thank you in advance!

 

2 REPLIES 2
Greg_Deckler
Super User
Super User

@kmpbi Are those tables related and if so on what column?


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

@Greg_Deckler , they are related on the Object column

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.