Forum Discussion

MatH's avatar
MatH
Frequent Visitor
4 years ago
Solved

Counting repeat callers

I want to be able to have a card that reports on repeat callers.

 

The table basically looks like this:

 

The CLI column is the users phone number, I wanted to be able to count how many times someone has called more than 5 times in a day. Then have a card that can report '25 people called more than 5 times in a day' I have a date table and slicers so hopefully this would the update to reflect that.

 

I know how to distict count to see how many unique callers, and also I managed to add a column which showed how many times that person had called in total. But not a way to just simplify it to be be X called >5 times.

 

I'm struggling with how to do this at the moment though, any help would be greatly appreciated.

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi MatH 
    Please try

    More Than 5 Calls =
VAR T1 =
    SUMMARIZE (
        TableName,
        TableName[CLI],
        TableName[Date],
        "@NumberOfCalls", COUNTROWS ( TableName )
    )
VAR T2 =
    FILTER ( T1, [@NumberOfCalls] > 5 )
VAR T3 =
    SELECTCOLUMNS ( T2, "@CLI", TableName[CLI] )
VAR Result =
    COUNTROWS ( DISTINCT ( T3 ) )
RETURN
    Result & "people called more than 5 times in a day"

3 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi MatH 
    Please try

    More Than 5 Calls =
VAR T1 =
    SUMMARIZE (
        TableName,
        TableName[CLI],
        TableName[Date],
        "@NumberOfCalls", COUNTROWS ( TableName )
    )
VAR T2 =
    FILTER ( T1, [@NumberOfCalls] > 5 )
VAR T3 =
    SELECTCOLUMNS ( T2, "@CLI", TableName[CLI] )
VAR Result =
    COUNTROWS ( DISTINCT ( T3 ) )
RETURN
    Result & "people called more than 5 times in a day"
    • MatH's avatar
      MatH
      Frequent Visitor
      4 years ago

      Thank you, I've just tried testing it and it seems to have worked perfectly. Much appreciated

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    I tried to create a sample pbix file like below.

    I hope the below can provide some ideas on how to create a solution for your data model.

    Please check the below picture and the attached pbix file.

     

     

    expected measure: =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Data[CLI] ),
            "@callcountperday", CALCULATE ( COUNTROWS ( Data ) )
        ),
        [@callcountperday] > 4
    )
)