“Frequent Caller” metric

[JCovelli]

Hello Group!

Does anyone know how I might use DAX or Power BI to calculate our “Frequent Caller” metric here at work?… we say that anyone of our members who has called us more than one time in a week is a “Frequent Caller”… the data set is made of rows, each row is a call, with the date of the call. The DAX calculation would need to know if the caller has called previously, and within 7 days of the previous call… any ideas?

[v-jiascu-msft]

Hi @JCovelli,

Can you share a dummy sample please? Then people here could gave back a solution demo.

Best Regards,

Dale

[JCovelli]

Hi Dale, I do not see a place to attach a file, nor insert a screen shot... what is the procedure for sharing with you? John

[v-jiascu-msft]

Hi @JCovelli,

I would suggest you create dummy sample, then you can share it with all of us. You can upload your file to the cloud drive like OneDrive, GoogleDrive and post the download link here. Or send us a private messages.

Best Regards,

Dale

[Anonymous]

Hi @JCovelli,

Do you have timestamp as well in your data or just days? 

[JCovelli]

Hello!

I get the data from a SQL query, and there is a time date field with a time stamp in it.  I usually do ,CAST(INTERACTION.PXCREATEDATETIME as DATE) so that I can group by DAY in a pivot table, but I can alos just leave it with the timestamp in it.

Thanks!

John

[Anonymous]

Hi @JCovelli,

Do you want to count the number of calls made in the same day as frequent calls or just on multiple days?

[JCovelli]

hmm... thats a good question... sometimes a caller might hang up and call right back, so It may be better to exclude calls made within the same day.. or within 30 minutes of each other... etc.

if this makes things too difficult with the calculation, then we could count include calls made in the same day.  

[Anonymous]

Hi @JCovelli,

To look at all the users who called on multiple days, you could create a measure Frq = DISTINCTCOUNT(Table[Date]). Then create a table visual with column User and Frq. Any row greater than 1 would be your frequent callers.

[JCovelli]

Hmm, but how could one tell between members who have called more than one time, versus those who have called multiple times withint a 7-day period?

[dramus · Accepted solution]

I created a new summarize table:

Last Called = summarize(Calls,Calls[Caller],"Last Called",max('Calls'[Date]))

Then I added a new Column:

Frequent Caller = if(countx(filter(Calls,datediff([Date],'Last Called'[Last Called],DAY)<=7 && [Caller]='Last Called'[Caller]),[Caller])> 1,"Yes","No")

Here's a pic of the report.

[JCovelli]

Thanks Rob for Solving the riddle!

John

[JCovelli]

This looks very interesting! I am also a beginner in alot of ways with this stuff, so please forgive the questions.

1. If you added some call instances for your Caller 3, for example a call on 1/12, another on 1/13, and another on 1/29, would the system you designed show Caller 3 as a Frequent Caller "Yes"

2. Would it show the number of calls within a 7-day period to be 3? 1/12, 1/13, and 1/15 and NOT include the call on 1/29?

[dramus]

I added some of the dates as you requested,

If you want a copy of my PBIX, PM me with your email address and I'll send you a copy.

There seems to be a kink in the # of calls in the last seven days, sometimes it get s the rigth answer and sometimes it over compensates by one.