Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I'm dealing with a lot of data from a call centre, and I am trying to investigate how many people contact us multiple times.
I can ise DISTINCTCOUNT to find out how many different numbers call us, and I can use COUNT to find how many times each number contacts us, but I want to be able to filter to find how many numbers there were that contacted us more than once. I tried using CALCULATE of the DISTINCTCOUNT, with the filter set as COUNT(Contact ID)>1, but it wouldnt let me, as COUNT is not a TRUE/FALSE criteria.
Where am I going wrong, what is the way round this?
Solved! Go to Solution.
@Anonymous ,
You may refer to the post below.
This may help.
Here's the mock data I used:
I created the following two measures:
Count of Phone Numbers = CALCULATE ( Count ( testCallCentre[Contact ID] ) )
and
Distinct Count of Phone Numbers = CALCULATE ( DISTINCTCOUNT ( testCallCentre[Contact ID] ) )
And then added a custom column that counts the number of times a [Contact ID] appears in the list:
Caller Count = COUNTROWS ( FILTER ( testCallCentre, testCallCentre[Contact ID] = EARLIER (testCallCentre[Contact ID] ) ) )
Then I apply the filter to the Frequent Callers calculation as follows:
Frequent Callers = CALCULATE ( [Distinct Count of Phone Numbers], testCallCentre[Caller Count] > 1 )
Here's the output:
Hope it works for you.
S.
Thanks,
Definitely along the right track, the only issue is that I need to be able to slice this down to a certain amount of time, and have it just show the number of repeat callers in that timeframe. Essentially, I need to be able to adapt the custom column so that it just checks against earlier calls within the sliced timeframe.
Have you tried using a date dimension to slice your central table?
Some more info about the data model you're working with would be helpful.
I have a date table linked to the call log that I can use to slice different dates. Ths issue is that using the method above I am pulling values a column that counts entries going all the way back to the start of the contact log. I need to be able to make it so that the new column only counts entries between the dates I am choosing on the slicer, to see how many people made multiple calls between selected dates.
@Anonymous ,
You may refer to the post below.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |