Forum Discussion
Identify hand off
Hi Everyone-
Been trying to figure out the approach for this problem for days. I have two tables that contain a unique ticket number and multiple Ticket numbers that have steps that a ServiceDesk Analyst used.
I want to know how many tickets are resolved and unresolved firsthand per team ( Unique Tickets #)
If a ticket needs to be resolved by another team that is not counted as resolved
Ultimately I would like to see the
Out of the total tickets (in table 1) created how many are resolved and unresolved based in (table 2).
Ticket closed as Team / Tickets created
The ratio of Tickets closed within the Team to Tickets created by the Team which is closed.
v_mark , You can have column like these
New column in Table 2 =
if(calculate(distinctCOUNT(Table2[Team]), filter(Table2, [Team] =earlier(Team]) )) >1, "Not resolved" , "Resolved")New column in Table 1 =
if(calculate(distinctCOUNT(Table2[Team]), filter(Table2, Table2[Team] = Table1[Team] )) >1, "Not resolved" , "Resolved")You can add condition for status as per need
7 Replies
v_mark , You can have column like these
New column in Table 2 =
if(calculate(distinctCOUNT(Table2[Team]), filter(Table2, [Team] =earlier(Team]) )) >1, "Not resolved" , "Resolved")New column in Table 1 =
if(calculate(distinctCOUNT(Table2[Team]), filter(Table2, Table2[Team] = Table1[Team] )) >1, "Not resolved" , "Resolved")You can add condition for status as per need
Hi v_mark,
Did any other fields that can be used as the index to trace these tickets' status? If not, I'd like to suggest you add them in 'query editor'.
Numbering Grouped Data in Power Query • My Online Training Hub
Then you only need to check the last status of the ticket to confirm if it has been resolved and the index number as parameter 'resolve efficiency' to compare with other tickets.Regards,
Xiaoxin Sheng
I appreciate your help. As far as an index to trace there's none. I followed the steps in the link. Now I'm trying to execute the next step based on your statement. Would you mind describing it a little more?
HI v_mark,
It sounds like you are consfued on coding formula. Can you please share some dummy data here with table format? Then we can coding formula based on your sample data.
How to Get Your Question Answered Quickly
In addition, you can also try to use following formula if it suitable for your scenario.
Masure = VAR currTicket = VALUES ( Table1[Ticket] ) VAR summary = SUMMARIZE ( FILTER ( ALLSELECTED ( Table2 ), [Ticket] IN currTicket ), [Ticket], [Team], [Status] ) RETURN DIVIDE ( COUNTROWS ( FILTER ( summary, [Status] = "Closed" ) ), COUNTROWS ( summary ) )Regards,
Xiaoxin Sheng
When you say
Then you only need to check the last status of the ticket to confirm if it has been resolved and the index number as parameter 'resolve efficiency' to compare with other tickets.
How can I execute that part?HI v_mark,
Write an expression with the current category to get the last index, then you can use index and category to lookup the last status.
Using the SELECTEDVALUE function in DAX - SQLBI
Understanding LOOKUPVALUE - SQLBI
After I douche check on your description I found the index fields seem not needed in your requirement. (they can be used when you need to find out team performance if all of the teams solved the tickets; the fewer result index number mean better performance)
Regards,
Xiaxoin Sheng
Anonymous and amitchandak .. Thank you for all the help. I appreciate it
