Forum Discussion
Double Relationship Between Two Tables
Hi Power BI Community!
I finally managed to create the waterfall visual I wanted using the solution tagged in below, which I am incredibly excited about. Unfortunately, I have now come across another issue when filtering for the data within my visual. When I click on my data, I only return data with open dates related to the visual, not showing the closed dates, which is very unfortunate.
Now my question is, is there a way I can connect both the open date and closed date to my date table? As a reference, my table looks like below and has a connection between my open date to my date table. I saw other solutions mentioning concatenating two columns, but I don't see that working for my two dates.
https://community.powerbi.com/t5/Desktop/Dax-Waterfall-Calculation-Help/m-p/1382921#M589473
| Ticket Number | Open Date | Close Date |
| 1 | 1/3/2018 | 4/8/2018 |
| 2 | 1/23/2018 | 4/2/2018 |
| 3 | 6/3/2018 | 8/9/2018 |
| 4 | 6/30/2018 | 9/4/2018 |
| 5 | 9/18/2018 | 10/3/2018 |
| 6 | 10/10/2018 | 9/9/2019 |
| 7 | 12/30/2018 | 1/2/2019 |
| 8 | 3/22/2019 | 7/1/2020 |
| 9 | 4/9/2019 | 8/9/2020 |
| 10 | 4/28/2019 | 9/8/2020 |
| 11 | 7/8/2019 | 1/2/2020 |
| 12 | 8/9/2019 | 3/9/2020 |
| 13 | 9/10/2019 | 10/9/2020 |
| 14 | 11/4/2019 | 4/9/2020 |
| 15 | 2/2/2020 | 9/3/2020 |
| 16 | 3/4/2020 | 8/4/2020 |
| 17 | 5/8/2020 | |
| 18 | 7/20/2020 | |
| 19 | 9/7/2020 | |
| 20 | 9/20/2020 |
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.
5 Replies
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 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.
