Forum Discussion
How to create relationship between one common table with two different tables
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
5 Replies
Anonymous 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/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?
Anonymous 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.
