topic Re: Find repeat values based on criteria in DAX Commands and Tips

Find repeat values based on criteria

jl02 — Sun, 21 Feb 2021 20:56:57 GMT

I’m hoping you all might be able to help me. I’m fairly experienced in Power BI but can’t figure this issue out.

I have a list of dates/times and phone numbers and departments (and some other data as well). What I am trying to do is figure out if a certain department is better at answer questions the first time, resulting in the fewest call backs.

For example, let’s say phone number 1234567890 called and was routed to Department Y. Then a week later they called again, and the call was routed to Department X. I want this to be counted as a repeat caller for Department Y (because they originally contacted Department Y first, so it’s department Y’s “fault” they are calling again).

Happy to clarify more if needed. I also don’t care if this is written with Dax or M.

I also just made a breakthrough where I was able to have a running count of whether the call was 1st, 2nd, 3rd, etc. Time the phone number has called. So now I just need something that says: If the number is greater than 1, then find the first instance of the number and return which department answered the call.

Thank you in advance.

Re: Find repeat values based on criteria

stevedep — Sun, 21 Feb 2021 18:19:11 GMT

Can you provide some sample data? This is doable.

Re: Find repeat values based on criteria

jl02 — Sun, 21 Feb 2021 19:48:57 GMT

Here is some made-up data similar to my own. Thank you, I appreciate your help!

Department PhoneNumber Agent Name Handled Call Start Date Time
Department Y 1234567890 Agent A 1 1/11/2021 0:00
Department X 7992770143 Agent B 1 1/11/2021 1:00
Department Y 2488783829 Agent C 1 1/11/2021 2:00
Department X 6835524237 Agent D 1 1/11/2021 3:00
Department X 2332764601 Agent A 1 1/11/2021 4:00
Department X 7313145153 Agent B 1 1/11/2021 5:00
Department X 4084243370 Agent C 1 1/11/2021 6:00
Department Y 8973413311 Agent D 1 1/11/2021 7:00
Department Y 6695770722 Agent A 1 1/11/2021 8:00
Department X 4633454011 Agent B 1 1/11/2021 9:00
Department X 4429767573 Agent C 1 1/12/2021 1:00
Department Y 7119122965 Agent D 1 1/12/2021 2:00
Department Y 5333900839 Agent A 1 1/12/2021 3:00
Department X 6646796325 Agent B 1 1/12/2021 4:00
Department Y 5928797526 Agent C 1 1/12/2021 5:00
Department Y 2632458048 Agent D 1 1/12/2021 6:00
Department X 3785692381 Agent A 1 1/12/2021 7:00
Department Y 3645446875 Agent B 1 1/12/2021 8:00
Department X 1234567890 Agent C 1 1/12/2021 9:00
Department Y 8684000132 Agent D 1 1/12/2021 10:00
Department X 3904431282 Agent A 1 1/13/2021 0:00
Department Y 3758663467 Agent B 1 1/13/2021 1:00
Department Y 8952477505 Agent C 1 1/13/2021 2:00
Department Y 2145389427 Agent D 1 1/13/2021 3:00
Department Y 5455993022 Agent A 1 1/13/2021 4:00
Department Y 8884283253 Agent B 1 1/13/2021 5:00
Department Y 5943076692 Agent C 1 1/13/2021 6:00
Department X 4602842729 Agent D 1 1/13/2021 7:00
Department Y 8027007073 Agent A 1 1/13/2021 8:00

Re: Find repeat values based on criteria

jameszhang0805 — Mon, 22 Feb 2021 04:46:39 GMT

Based on your provided data source.
PBIX FILE

Re: Find repeat values based on criteria

stevedep — Mon, 22 Feb 2021 05:58:11 GMT

jl02 , Not sure if its already answered. But I think you want to check if somebody took the call first and then check to see if they called again later (within a certain time frame, set by parameter). This is what I have for you:

First check if they called again later:

NoOfCallsbyNumberLaterOn = VAR _selNo = SELECTEDVALUE('Table'[PhoneNo]) VAR _DT = SELECTEDVALUE('Table'[DT]) VAR _DTEnd = _DT + SELECTEDVALUE(NoOfDays[NoOfDays]) RETURN COUNTROWS(FILTER(ALL('Table'),[PhoneNo] = _selNo && [DT].[Date] > _DT && [DT].[Date] < _DTEnd))

Check if they were the first one to take the call:

OrderOfCallTakers = VAR _selNo = SELECTEDVALUE('Table'[PhoneNo]) VAR _DT = SELECTEDVALUE('Table'[DT]) VAR _DTEnd = _DT + SELECTEDVALUE(NoOfDays[NoOfDays]) RETURN COUNTROWS(FILTER(ALL('Table'),[PhoneNo] = _selNo && [DT].[Date] <= _DT && [DT].[Date] < _DTEnd))

Check if there were repeat call and they were the first one to take the call:

FirstCallTakerwithRepeatCalls = COUNTROWS(FILTER('Table', [NoOfCallsbyNumberLaterOn] > 0 && [OrderOfCallTakers] = 1))

Finally, per call taker count how many instances they had where they were the first one to take the call and the customer called again later (within the set time frame):

NoOfRepeatCalls = COUNTX('Table',[FirstCallTakerwithRepeatCalls])

Hope it helps.

Kind regards, Steve.

file is attached.