first call resolution

[Anonymous]

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 tried the solution from this question but it didn't work because my variable (customer_id) isn't constant. 

An exemple of the dataset:

customer_id	contact_date	issue_id
1	2020-05-07	770
2	2020-05-05	772
2	2020-05-05	772
2	2020-05-07	764
3	2020-05-20	759
4	2020-05-14	673
4	2020-05-20	772
5	2020-05-12	741
5	2020-05-15	677
5	2020-05-15	677
6	2020-05-06	637
6	2020-05-15	772
7	2020-05-05	772
7	2020-05-06	746
7	2020-05-07	753
7	2020-05-07	753
8	2020-05-06	755

The idea is to calculate the data like this, when the same customer contacts us but for different issue, count = 1.

I have other filters besides day by day like the exemple below that I would like to see such as FirstOfMonth, FirstOfWeek, Resolver etc. However, FCR for day, week and month are the most interesting matrics for my team.

customer_id	contact_date	issue_id	count
1	2020-05-07	770	1
2	2020-05-05	772	2
2	2020-05-07	764	1
3	2020-05-20	764	1
4	2020-05-14	673	1
4	2020-05-20	772	1
5	2020-05-12	741	1
5	2020-05-15	677	2
6	2020-05-06	637	1
6	2020-05-15	772	1
7	2020-05-05	772	1
7	2020-05-06	746	1
7	2020-05-07	753	2
8	2020-05-06	755	1

Then we calculate,

FCR = count(customer_id where count = 1) / count(customer_id)

Using the example above, 

FCR = 11/14 = 0.78 (*in general, FCR is different if we plot it day by day or month by month

I need a few measures to calculate the numerator and denominador for calculation.

Could anyone help me? Thanks in advance!

Best regards,

Steven

[jorge_oliveira]

Hello,

I need to measure First Contact Resolution (FCR), with more conditions than the original post. But I'm having trouble doing this with DAX.

Example of the dataset:

I highlighted the last 4 lines because the contacts happened in the same day, so they need a different treatment.

I have to calculate FCR in two different ways:

1. If the same customer doesn't contact again 3 days after the first contact it's a first contact resolution.

In this first calculation, we only consider the customer and the contact date.

Some conditions:

- If the customer contacts more than one time in the "first day", we only count as one.

- If we have a contact within 3 days after the first contact, and no contact after that second contact, this second contact is not a first contact resolution.

The result I expect:

As you can see, I only count one contact for the customers H and I.

1. If the same customer doesn't contact again 3 days after the first contact with a contact from the same type, we consider it a first contact resolution.

Some conditions:

- If the customer contacts more than one time in the "first day" with the same contact type, we only count as one.

- If we have a contact from the same type within 3 days after the first contact, and no contact from the same type after that second contact, this second contact is not a first contact resolution.

The result I expect:

In this example, customer I counts with only one contact.

I also need it to work with slicers. Here's an example with the type dimension in the slicer and the type 1 selected:

Thanks for all the help.

Best regards.

Jorge

[Anonymous · Accepted solution]

// 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

[Anonymous]

Hi @Anonymous,

I modified your code and the result is what I got from my SQL. When I was solving the problem I wasn't exactly sure how I could measure this.

Thanks for your help! 

Best regards,

Steven

[Greg_Deckler]

@Anonymous 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

[Anonymous]

Hi @Greg_Deckler, I will try it and get back to you. Thanks for responding!