Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
  • Anonymous's avatar
    Anonymous
    6 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