Can some DAX expert help me on below requirement please

[DeeMag]

Hi all,

Date Set : As given above, I have a dataset which has callers and dates on which they called.

Requirement : To find wherther the call by caller is unique or not in when checked in past 3 days.

example : on 3rd Sep caller 1021 has called, if you observe in the screen shot, there were no calls from 1021 in the past consecutive 3 days, so it should be marked as unique. This should be checked for all days for all the callers.

 If you need more explanation on this, let me know.

I hope someone can help me on this please an d appreciate your help.

Thank you.

[richbenmintz · Accepted solution]

Hi @DeeMag ,

Based on your data, you can use the following calc column to identity the type of call

Call Type = 
var _caller_id = 'Table'[Caller]
var _curr_date = 'Table'[Date]
var _prior_max_date = CALCULATE(MAX('Table'[Date]), FILTER(all('Table'), 'Table'[Date]<_curr_date&& 'Table'[Caller] = _caller_id))
var _date_diff = DATEDIFF(_prior_max_date,_curr_date, DAY)
return 
if(_date_diff<=3&&NOT(ISBLANK(_date_diff)), "repeat", "unique")

Thanks,

[Greg_Deckler]

@DeeMag Is that how your data looks in your model? If so, I would unpivot your date columns and then the issue should be straight-forward. 

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, 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

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[DeeMag]

Hi Greg,

Thanks for getting back.

actually the data does not look like that. I have download and made it look that way for easier understanding of logic.

The data come from a database and on data sheet it looks like this and has other columns as well.

Regards,

Deepthi

[richbenmintz]

Hi @DeeMag,

Can you please provide the data in a table and not an image, makes the process of delivering a solution much easier.

Thanks,

[DeeMag]

Hi Richard,

Is there any way to upload table?

Regards,

[DeeMag]

Hi Greg,

I downloaded this report to excel from Power BI and just Pivoted it by counting callers by day.

Ignore Queue column.

[DeeMag]

Queue	Caller	Date	Total calls
Abandoned	2389	16/08/2021 00:00	1
Abandoned	7933	17/08/2021 00:00	1
Abandoned	3534	18/08/2021 00:00	2
P302	3534	18/08/2021 00:00	1
Abandoned	1021	18/08/2021 00:00	2
Abandoned	1021	23/08/2021 00:00	1
Abandoned	1022	23/08/2021 00:00	3
Abandoned	1023	23/08/2021 00:00	4
Abandoned	1060	24/08/2021 00:00	2
Abandoned	7483	24/08/2021 00:00	1
P360	2389	24/08/2021 00:00	1
Abandoned	1021	24/08/2021 00:00	2
Abandoned	7476	25/08/2021 00:00	2
Abandoned	1761	25/08/2021 00:00	3
Abandoned	1557	25/08/2021 00:00	2
Abandoned	4092	25/08/2021 00:00	1
Abandoned	7933	25/08/2021 00:00	1
Abandoned	1761	26/08/2021 00:00	1
Abandoned	7483	27/08/2021 00:00	8
Abandoned	1761	27/08/2021 00:00	1
Abandoned	702	31/08/2021 00:00	3
Abandoned	2389	31/08/2021 00:00	1
P360	2389	31/08/2021 00:00	1
Abandoned	4936	31/08/2021 00:00	3
P301	4936	31/08/2021 00:00	1
P301	8454	02/09/2021 00:00	1
Abandoned	1023	02/09/2021 00:00	1
Abandoned	1021	03/09/2021 00:00	7
Abandoned	1022	03/09/2021 00:00	4
Abandoned	1023	03/09/2021 00:00	3

actually total calls  will be 1, 2,3 etc not in the format you see.

[richbenmintz · Accepted solution]

Hi @DeeMag ,

Based on your data, you can use the following calc column to identity the type of call

Call Type = 
var _caller_id = 'Table'[Caller]
var _curr_date = 'Table'[Date]
var _prior_max_date = CALCULATE(MAX('Table'[Date]), FILTER(all('Table'), 'Table'[Date]<_curr_date&& 'Table'[Caller] = _caller_id))
var _date_diff = DATEDIFF(_prior_max_date,_curr_date, DAY)
return 
if(_date_diff<=3&&NOT(ISBLANK(_date_diff)), "repeat", "unique")

Thanks,

[DeeMag]

Excellent, Thank you so much, it works!!

[richbenmintz · Accepted solution]

Hi @DeeMag,

Happy to help!

[DeeMag]

Hi Richard,

Earlier you helped on this request, Thanks for that. 

But, I need a small tweak on existing code please.

If the call appears on same day, 1st call should be unique and other calls on same day should be repeat. can you please help on this.

Let me know if you need more details.

Thank you, appreciate your help on this.

[richbenmintz]

Hi @DeeMag,

How do we Know which is the first call? the Date does not provide a call time?

[DeeMag]

Thanks much Richard, I will try it and update you on this.