cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tlenzmeier Regular Visitor
Regular Visitor

Filtering Dates Across Multiple Tables

Hello,

 

I have an issue where the user has to select three dates from three different date slicers. One table is a customer invoice table, the next table is a customer payments table with a foreign key linked back to the invoice table. The third table is an orphan table (no joins to any table) that is used to determine the days past due of an invoice. If I join my date dimension to the invoice table, all is well and good. However, if I attempt to join the date table to the payment date from the payments table, the join isn't active due to the existing relationship to the invoice table. Rather than have users select three dates that are all the same, it would be ideal if they selected one date and achieved the following: 1. all open invoices as of a point in time, 2. all payments received as of that point in time, and 3. the days past due on any remaining balance as of that point in time. I've tried using a RELATED function, but that does not seem to work. I've also tried creating a bridge table, but that hasn't worked either. I may be doing it all wrong, but it would be convenient to the user if I could simplify things.

3 REPLIES 3
Super User IV
Super User IV

Re: Filtering Dates Across Multiple Tables

Check out the USERELATIONSHIP function and see if it gets you there:

 

https://msdn.microsoft.com/en-us/library/hh230952.aspx


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

tlenzmeier Regular Visitor
Regular Visitor

Re: Filtering Dates Across Multiple Tables

Here's my model. If I could trouble you for some recommendation(s) on what to use as inactive joins. The "PD Calendar" is used to calculate the days past due. The join from invoice to payment is on the primary key. The only active join insofar as dates, is from date to GL Date on the invoice table.

 

2017-12-20 14 20 30.pngCurrent model . . .

Community Support
Community Support

Re: Filtering Dates Across Multiple Tables

Hi @tlenzmeier,


I don't think you can direct use a unrelated table to filter on other tables.
Please at least create one relationship to these tables to apply the filter effect or try to use measure to filter on visual level filter.

 

Regards,

XIaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors