Forum Discussion
Anonymous
6 years agoNot applicable
first call resolution
Dear community, I am a handicap when it comes to measures or any dynamic filtering. I like to measure FCR for my organization and I have spent a long time to make this work but I couldn't. I t...
- Anonymous6 years ago
// T is the first dataset (not the second, aggregated). // There must be a Date table in the model // marked as Date table that joins to // T on [contact_date] and the filtering is // one-way. There also has to be a Customer dimension // in the model that joins on CustomerId to // T on [customer_id]. All columns of the fact table // T must be hidden and slicing is only allowed via // dimensions. If you want to slice by Issue, // you have to create a dimension Issue and join // to T on [issue_id]. In the code below I've assumed // that you won't be slicing by issues. // Then you can create measures: // This, for any slicing, will tell you // how many rows are visible in the current // context, which means "number of calls." [# Calls] = COUNTROWS( T ) // This will tell you the percentage of // customers visible in the current context // that got their issue resolved in the first // call. [First Call Resolution %] = var __existingCustIssueTable = SUMMARIZE( T, Customer[CustomerID], T[issue_id] ) var __custIssueWithOneCallResolution = SUMX( __existingCustIssueTable, ( [# Calls] = 1 ) * 1 ) var __custIssueTotal = COUNTROWS( __existingCustIssueTable ) RETURN DIVIDE( __custIssueWithOneCallResolution, __custIssueTotal )If you decide to introduce a separate Issue dimension, you'll need to adjust the code, especially what's under SUMMARIZE. Here's the code after the required changes for this scenario:
[First Call Resolution %] = var __existingCustIssueTable = SUMMARIZE( T, Customer[CustomerID], Issue[IssueID] ) var __custIssueWithOneCallResolution = SUMX( __existingCustIssueTable, ( [# Calls] = 1 ) * 1 ) var __custIssueTotal = COUNTROWS( __existingCustIssueTable ) RETURN DIVIDE( __custIssueWithOneCallResolution, __custIssueTotal )By the way, your specification of the problem is a bit misleading. You cannot just count customers in the current context. You have to calculate the pairs "customer-issue" to get the correct percentage.
Best
D
Greg_Deckler
Community Champion
6 years agoAnonymous didn't follow all of this but for a resetting counter, you need Cthulhu. https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211
- Anonymous6 years agoNot applicable
Hi Greg_Deckler, I will try it and get back to you. Thanks for responding!