Tracking repeat callers per day based on distinct call ID

[Caseyc1983]

I currently have a table which list all calls received each day into a contact centre and from this I am looking to pull out data relating to the number of repeat customers (distinct telephone numbers) who called on daily basis & of those calls, how many times did those particular repeat customers call on each given day.  I currently have the following DAX which works to an extent  but is causing memory failure error and I am looking for a workaround for this if possible?  I am also looking to see if we can adapt the 2nd dax measure to only bring back total calls from repeat callers based on a distinct call ID but I am struggling to incorporate this into the measure so any help would be much appreciated.   Thanks 

No Distinct Repeat Callers =

var ValidCalls = FILTER('Repeat Callers',Not 'Repeat Callers'[Originator DN (Calling Number)] IN {"Anonymous", "Withheld", BLANK()})

var NumberOfRepeatCustomers =

COUNTROWS (

filter (

ADDCOLUMNS (

SUMMARIZE (

ValidCalls,

'Repeat Callers'[Originator DN (Calling Number)]),

"TC", CALCULATE(sumx('Repeat Callers', if (COUNTROWS('Repeat Callers')>1,1,0)))

) ,

[TC]>0)

)

return

NumberOfRepeatCustomers

Repeat Callers - Total Calls =

var ValidCalls = Filter('Repeat Callers',Not 'Repeat Callers'[Originator DN (Calling Number)] IN {"Anonymous", "Withheld", "Private",BLANK()})

var tablevar =

SUMMARIZE(

ValidCalls,

'Repeat Callers'[Originator DN (Calling Number)],

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

) )

return

SUMX(tablevar, [TC])

[danno]

@Caseyc1983  It looks like there is a lot going on inside your measures, including a lot of iterative functions.  I would suggest creating summarised calculated tables at the required level of granularity, rather than trying to calculate these measures on the fly, that way the calculations are pushed down into the model refresh, rather than being done while the report is running.  
If you need to indicate which callers are repeat callers, you could put this logic into a calculated column, again this would push some of the logic into the model refresh, rather than doing is all in the measure. 

Let me know if this helps

[Caseyc1983]

Thank you so much for the reply guys.  I like the idea of pushing this into a refresh summarised calculated table, can you give me an idea of  how I would go about doing this as it is not something I have done before.  To give you a rough idea of what the data would look like, I have put a small table below.  The actual dataset is made up of over a million lines at present.

Date	Call ID	Telephone Number	Queue
01/01/20	12345	66666666666	Queue 1
01/01/20	12346	66666668888	Queue 2
01/01/20	12347	66666666898	Queue 2
01/01/20	12348	66666666666	Queue 2
01/01/20	12349	66666666666	Queue 3
01/01/20	12350	66666666669	Queue 1
02/01/20	12351	55555555554	Queue 1
02/01/20	12352	88888888881	Queue 2

[danno]

You can sumarise callers into a smaller table using this table expression: 

Repeat Callers = FILTER(SUMMARIZECOLUMNS(Calls[Telephone Number], "Calls", COUNTROWS(Calls)), [Calls] > 1)

you could then use the numbers in this table combined with a measure for the number of calls to report on repeat callers in a single day. 

Repeat Callers = COUNTROWS('Repeat Callers')

Calls = COUNTROWS('Calls')

please mark this as a solution if it has helped you

[Caseyc1983]

This is fantastic, thank you very much.  I am pretty much there but I wonder if there is a way of only pulling back repeat calls which only have a unique call ID?  Some repeat calls that come through have multiples of the same call ID (agents not answering phones and call bouncing to next available agent) and I don't want to count these.   Really appreciate all your help.

[Greg_Deckler]

Very difficult to troubleshoot without sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

That said, see my series on performance tuning DAX: https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275