How to create relationship between one common table with two different tables

[musmanrafiq]

Hello, 

I have 3 tables

1. Users (UserID, SignUpDate)
2. Tests (TestID,TestDate,UserID)
3. Calender (Date)

I want to use the Calender table date to filter my whole report which is created on the first two tables.

The problem I am facing is that If I create a relationship between Tests and Calender, I cannot create a relationship between Users and Calender table.

How I can I use one common Calender table to filter both table using Slicer?

Expected Output: User Date Range Slicer to filter the first two tables' data

Thanks

[v-yiruan-msft]

Hi  @musmanrafiq ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

[SpartaBI]

@musmanrafiq I guess you also have a relationship between users and tests and I guess it's 1 to many.
You have 2 options:
1. Merge the users and tests table before uploading to the model and create two relationships between the date table and two date columns (one active and one inactive and activate the other when needed through USERELATIONSHIP()
2. Make one of the relationships inactive and activate it when needed through USERELATIONSHIP().

I would go with option 1

Check out this article and video:
https://www.sqlbi.com/articles/using-userelationship-in-dax/

Showcase Report – Contoso By SpartaBI

     

[musmanrafiq]

Thank you @SpartaBI for the reply. 

These two measures are created based on the first two tables.

1st Measure belongs to Users tables

2nd Measure belongs to TestsTable

If I make one relationship inactive, will it update both values simultaneously based on slicer selection?  

[SpartaBI]

@musmanrafiq you will need to use USERELATIONSHIP() inside of calculate to make it active for the duration of the measure execution. Check out the article and update me if you succeeded. 

[musmanrafiq]

I will give it a try and will let you know.

Thanks