Filter Relative time data - not related tables in a matrix.

[melvin9900]

Hello,

   I need assistance in filtering the data without a direct relationship between tables. We have three tables:

1. Sales
2. Time (which contains calendar data)
3. Relative Time (loaded daily with relative dates and descriptions such as 'Current Month', 'Next Month', 'Prior Week', 'Current Week', 'Rolling 12', 'Prior Rolling 12', and 'Current Year').

I need to develop a report that shows the Sales amount and Sales quantity for each sales representative based on multiple relative time selections made in the slicer. It should look something like this.

I set up the relationships between the tables as described below, but the filter isn't working, and I can't use the Sales and RelativeTime fields in the matrix because they aren't directly related.

I can't create a relationship between Sales (date) and RelativeTime (date) because of a many-to-many relationship. To solve this, I added a Time table with distinct dates and linked both Sales and RelativeTime to it. Unfortunately, this hasn't resolved the issue.

Sample Data :

Please help me figure out how to filter and display the data effectively under these conditions. Any recommendations are appreciated

[johnt75 · Accepted solution]

This is one of the few occassions when you want to use a bidirectional filter. Make the relationship between Time and Relative Time bidirectional, so that when you place a filter on Relative Time that filter will reach the Time table, and ultimately Sales.

[melvin9900]

Thank you. It worked.