topic Re: Total Calls for repeated telephone numbers in DAX Commands and Tips

Total Calls for repeated telephone numbers

Caseyc1983 — Fri, 10 Jan 2020 14:21:39 GMT

Hello,

I am looking to find a calculation that will give me a value for the total number of calls per day, week, month, from telephone numbers which have contacted us on more than one occassion. My table list all calls by date, telephone number, and a unique call reference number. Total should exclude calls from customers who have only contacted us once during the timeframe specified. Any help would be much appreciated.

ID	Date	Telephone Number
1	04/11/2019	12345
2	04/11/2019	13246
3	04/11/2019	12222
4	04/11/2019	12345
5	04/11/2019	12345

Re: Total Calls for repeated telephone numbers

Anonymous — Fri, 10 Jan 2020 21:18:55 GMT

Hi Caseyc1983 ,

This measure works for me, but only in a matrix, where the timeframe is still part of the evaluation context.

My Measure:

M1 :=

var tablevar = SUMMARIZE(

Calls,

Calls[callerid]

, "TC", sumx(Calls, if (COUNTROWS('Calls')>1,1,0)

)

return

SUMX(tablevar, [TC])

Hope this helps, if not then I am also interested in the final solution 🙂

regards,

Jan

Re: Total Calls for repeated telephone numbers

Caseyc1983 — Sun, 12 Jan 2020 08:11:47 GMT

Thank you so much for this, worked a treat. Is there anyway of applying some form of filter to this calculation in order to exclude any calls from numbers which are witheld or anonymous? Likewise, do you have a solution which counts the distinct number of customers who have called more than once on a given day, week or month? This would also need to have the ability to filter / exclude certain things such as witheld numbers

Really appreciate you getting back to me!!

Re: Total Calls for repeated telephone numbers

Anonymous — Sun, 12 Jan 2020 11:31:17 GMT

Hi Caseyc1983 ,

the first question can be covered with the measure like:

M2 = 
var ValidCalls = Filter(Calls,Not Calls[callerid] IN {"Anonymous", "Withheld", "Private"})
var tablevar = 
 SUMMARIZE(
 ValidCalls, 
 Calls[callerid],
 "TC", sumx(Calls, if (COUNTROWS('Calls')>1,1,0)
 ) )
return
SUMX(tablevar, [TC])

The first parameter of Summarize exepts a table. If you change the ValidCalls varible you can control the table that is used by the summarize statement.

DistinctCustomers per period is basically an additional wrapper around the summarize command:

M3 = 
var ValidCalls = Filter(Calls,Not Calls[callerid] IN {"Anonymous", "Withheld", "Private"})
var NumberOfDistinctCustomers = 
COUNTROWS (
filter (
    ADDCOLUMNS (
        SUMMARIZE (
     ValidCalls, 
     Calls[callerid]), 
 "TC", CALCULATE(sumx(Calls, if (COUNTROWS('Calls')>1,1,0)))
    ) ,
    [TC]>0)
    )
return
NumberOfDistinctCustomers

Give it a try

Re: Total Calls for repeated telephone numbers

Caseyc1983 — Sun, 12 Jan 2020 19:43:26 GMT

Thank you so much guys, you're help is much appreciated!!!!

Re: Total Calls for repeated telephone numbers

Caseyc1983 — Sun, 12 Jan 2020 20:00:20 GMT

Sorry to be a pain but how would I add 'is not blank' to those filters as well.

thanks

Re: Total Calls for repeated telephone numbers

Caseyc1983 — Sun, 12 Jan 2020 20:07:06 GMT

Just ignore that, figured it out. Just being daft!