Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
JCovelli
Helper I
Helper I

“Frequent Caller” metric

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?

1 ACCEPTED SOLUTION
dramus
Continued Contributor
Continued Contributor

 

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.

Frequent Callers.PNG

View solution in original post

13 REPLIES 13
v-jiascu-msft
Microsoft Employee
Microsoft Employee

Hi @JCovelli,

 

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

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @JCovelli,

 

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

 

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
Not applicable

Hi @JCovelli,

 

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

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
Not applicable

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.

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
Continued Contributor
Continued Contributor

 

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.

Frequent Callers.PNG

Thanks Rob for Solving the riddle!

 

John

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
Continued Contributor
Continued Contributor

I added some of the dates as you requested,

 

Frequent Callers.PNG

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.