Count tickets assigned and tickets resolved

[Anonymous]

Hello community!

i'm in need of help, I'm not able to get the total of tickets assigned and resolved for each member of my team.

On powerbi I have 3 tables, Calendar Table, Incidents table & Teams Table. 

Incidents:

Team:

I want to get the total created, resolved and assigned tickets for each team member.

The current active relationship is Incidents[assigned to] with Team[names]

Total Resolved = COUNTA(Incidents[resolved_by])

Total Assigned To = COUNTA(Incidents[assigned_to])

Both of this measures return the same number, but it should be different

I'm lost and don't know how to get the right number for each column, it seems the relationship is only counting the "assigned to" column regardless.

thanks in advance

[amitchandak · Accepted solution]

@Anonymous , You can join both Created date and resolved date with the calendar table. One join will inactive, You can activate that usinguserelationhip

1n case your date has timestamp, create the date column first

Open Date  = Datevalues([Sys Created Date])  // do same for resolve date

calculate( count(Table[ticket]),USERELATIONSHIP ('Table'2[Open Date ], 'Date'[Date]))

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

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

[Anonymous]

Thank you @amitchandak 

One question, will this relationship with dates will allow me to see the tickets created / tickets resolved by each team member, even if there is no relation between [Created By] and [Resolved By]?

[Icey · Accepted solution]

Hi @Anonymous ,

I think this may be what you want:

Total Resolved = CALCULATE( COUNTA(Incidents[resolved_by]),USERELATIONSHIP(Incidents[resolved_by],Team[Full Name]))

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Anonymous , You can join both Created date and resolved date with the calendar table. One join will inactive, You can activate that usinguserelationhip

1n case your date has timestamp, create the date column first

Open Date  = Datevalues([Sys Created Date])  // do same for resolve date

calculate( count(Table[ticket]),USERELATIONSHIP ('Table'2[Open Date ], 'Date'[Date]))

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

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