Dax formula help: Sorting distinct entries by frequency

[Anonymous]

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?

[sanct]

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.

[Anonymous]

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.

[sanct]

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.

[Anonymous]

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.

[v-chuncz-msft · Accepted solution]

@Anonymous ,

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-in/td-p/495848