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