Filtering Dates Across Multiple Tables

[tlenzmeier]

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.

[v-shex-msft]

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

[Greg_Deckler]

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

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

[tlenzmeier]

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.

Current model . . .