Forum Discussion
Double Relationship Between Two Tables
Hi Anonymous ,
You may create an active relationship on 'Date table'[Date] and 'Fact table'[Date1] , and then create an another inactive relationship on 'Date table'[Date] and 'Fact table'[Date2] . Once you need the relationship about 'fact table'[Date2], just to create column or measure using USERELATIONSHIP like DAX below. ( Note that the [Date1] could be the frequently used one. In your scenario, the [Date1] may be [Open Date], and the [Date2] may be [Close Date] ),
Total Sales= CALCULATE (SUM('fact table'[Sales]) ),USERELATIONSHIP ( Date[Date], 'fact table'[Date2] ))Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous , Join both of them with date table. One will active another one will be inactive. you can use userelation to use the other join.
But in case you need to filter both open and close on different time ranges then use two date tables or use slicer from these dates only.
Hi amitchandak! Thank you again for your help and referencing your HR analytics article!
Would you mind clarifying what you mean by joining both of them with the date table? I turned on the Open Date relationship with the Date table, but I cannot turn on the Closed Date relationship with the Date table since I can only have one relationship between two tables I believe. I used the two HR Employee and Terminated Employee formulas for my one calculation, but I currently cannot connect both dates to the Date table.
Anonymous , You should able to join both open and close date with date tables. Only one join will be active. For others, you have use userelation.
In case open and close date need to run on different months(say) you need use that date slicer or two date tables.
what is expected output
Hi amitchandak, just to clarify, I am using both the open and close dates in my calculation but only able to reference the open date. I currently have two visuals: waterfall (showing different between tickets opened and closed by month) and table (showing a list of tickets regardless of opened and closed dates). I would like to click on a bar in my waterfall and show me both the tickets that were opened and closed that month. It's not currently possible with my connections because I only have the open date relationship on and cannot activate the close date relationship. Any idea to showcase both dates in my visuals?