cancel
Showing results for
Did you mean:
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
Community Support

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.
5 REPLIES 5
Frequent Visitor

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.

Frequent Visitor

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.

Frequent Visitor

Have you tried using a date dimension to slice your central table?

Frequent Visitor

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.

Community Support

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.