Merge two table

[GMallory]

Hello,

I have the following model view :

I have two table "suivi_case", "suivi_case_dupl" who is a duplication of "suivi_case" and a custom date table.
The "suivi_case" table is in direct relationship with the date table through the column "suivi_case[Sumbit_Date]" -> "Date[Date]"
The "suivi_case_dupl" table is in direct relationship with the date table through the column "suivi_case_dupl[Close_Date]" -> "Date[Date]"

The model is in this way because I can't have two direct relationship between table (see: https://learn.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive ) and I need it two display open/close hotline ticket bar chart:

In both table, "suivi_case" and "suivi_caset_dupl", I have a third column "Age" who contain the age in number of weeks of each ticket.

Now, I want to display in a pie chart the number/percentage of ticket split into three categories according to the following aging (calculated column) :
Aging = IF(cr5b0_suivi_case[Age] <= 6, " - de 7 wks", IF(cr5b0_suivi_case[Age] > 8, "+ de 8 wks", " 7 et 8 wks"))

I can easily display two pi charts, one for "suivi_case" and one for "suivi_case_dupl". But I want to have only one pie chart who will take data from both "suivi_case" and "suivi_case_dupl".

For example, I'm able to display with a table visual, (for a period of one week) the open tickets from "suivi_case" with their related aging.
In the same way, I'm displaying close tickets from "suivi_caset_dupl" with their related aging.

Now, how I can merge these two 'tables' ? We need to be careful that there is duplication of id when a ticket was open and close in the same selected period (in red rectangle on above picture), and we don't want to count a same ticket twice.

Thanks for your help,

[GMallory]

Hello @amitchandak ,

Thanks for your quick answer.
With your advice, I was able to remove my duplicate table and create successfully my pie chart.

According to your message, I create a new measure :

calculate_close_date = calculate(Countrows(suivi_case), USERELATIONSHIP(suivi_case[Closed_Date], 'Date'[Date]))

After put it on column y-axis of my bar chart, it's work fine.

Then, to create my pie chart, I created a new column in my table :

Aging = IF(suivi_case[Age] <= 6, " - de 7 sem", IF(suivi_case[Age] > 8, "+ de 8 sem", " 7 et 8 sem"))

Finally, I create three new measure :

- 7 sem = calculate(Countrows(suivi_case), USERELATIONSHIP(suivi_case[Closed_Date], 'Date'[Date]), suivi_case[Age] < = 6 ) + calculate(Countrows(suivi_case), suivi_case[Age] < = 6 ) 

betw 7 & 8 sem = calculate(Countrows(suivi_case), USERELATIONSHIP(suivi_case[Closed_Date], 'Date'[Date]), suivi_case[Age] > 6 && suivi_case[Age] < = 8 ) + calculate(Countrows(suivi_case), suivi_case[Age] > 6 && suivi_case[Age] < = 8 ) 

+ 8 sem = calculate(Countrows(suivi_case), USERELATIONSHIP(suivi_case[Closed_Date], 'Date'[Date]), suivi_case[Age] > 8 ) + calculate(Countrows(suivi_case), suivi_case[Age] > 8 ) 

In my pie chart, I place Aging column as Legend and the three measures as Values.
It's work really fine and data are correct.

However, I still face some issues.

When I want to interact with my pie chart with the highlighting functionality, it's work partially.
For example, if I select the red 16% in my bar chart, only open case change dynamically on my bar chart.

In the same way : I create a new column to set an "exclude closed case" slicer.

ExcludeClosed = IF(cr5b0_suivi_case[Status] <> "Closed", "Exclure", BLANK())

But when I check the slicer, the "open tickets" bars are updated, but nothing about "close tickets" bars (which should

disappear completely).

How I can fix these broken behaviors ?

Thanks a lot.

[GMallory]

@amitchandakSorry, now all is working perfectly !
I don't know what I did, but I play a bit with the "edit interaction" tool, and now it's working fine.

Thank you very much !

[amitchandak · Accepted solution]

@GMallory , No need to duplicate table, You can use inactive relationship and use userelationship in measure to use another join

One date Table - Two join - On inactive , assume CloseDate is inactive

calculate( Countrows(suivi_case),USERELATIONSHIP ('suivi_case'[CloseDate], 'Date'[Date]))

refer

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...