Double Relationship Between Two Tables

[Anonymous]

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

[v-xicai · Accepted solution]

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.

[amitchandak]

@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.

https://youtu.be/e6Y-l_JtCq4

Or https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

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.

[Anonymous]

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.

[amitchandak]

@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

[Anonymous]

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?