cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bsampson1990
Frequent Visitor

Dax formula help: Sorting distinct entries by frequency

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?

1 ACCEPTED SOLUTION

@bsampson1990 ,

 

You may refer to the post below.

https://community.powerbi.com/t5/Desktop/DAX-to-count-rows-with-same-value-for-Column-A-for-a-value-...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
sanct
Frequent Visitor

This may help.

 

Here's the mock data I used:

 

callcentre.png

 

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:callCentreOutput.png

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.

@bsampson1990 ,

 

You may refer to the post below.

https://community.powerbi.com/t5/Desktop/DAX-to-count-rows-with-same-value-for-Column-A-for-a-value-...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors