Forum Discussion
Tracking repeat callers per day based on distinct call ID
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
5 Replies
- Greg_Deckler
Community Champion
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
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
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 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
