Forum Discussion

jl02's avatar
jl02
Frequent Visitor
5 years ago

Find repeat values based on criteria

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 dat...
DAX
M Query
stevedep's avatar
stevedep
Memorable Member
5 years ago

Can you provide some sample data? This is doable.

jl02's avatar
jl02
Frequent Visitor
5 years ago

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

  • stevedep's avatar
    stevedep
    Memorable Member
    5 years ago

    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.