Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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?

Need Help

5 Replies

  • sanct's avatar
    sanct
    Frequent Visitor
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        sanct
        Frequent Visitor
        7 years ago

        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.