Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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:
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.
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
// 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
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
@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
User | Count |
---|---|
58 | |
21 | |
18 | |
16 | |
13 |
User | Count |
---|---|
85 | |
54 | |
45 | |
39 | |
21 |