Calculate DateTime Diff In Group BY with DAX

[GOT2021]

Hi, my problem was this : 

Date	Id	User	Job
10/01/2024 15:01:24	001	Adam	Nurse
10/01/2024 15:11:24	001	Max	Nurse
10/01/2024 16:01:24	001	George	Doctor
15/01/2024 08:00:00	002	Michel	Nurse
17/02/2024 17:00:50	003	Max	Nurse
1702/2024 14:10:00	003	George	Doctor
17/02/2024 15:26:00	003	Patrick	Doctor
25/03/2024 14:00:00	004	George	Docor
26/03/2024 12:25:00	005	Anne	Nurse
26/03/2024 14:15:00	005	Sophie

For each ID I would like to keep at most 2 ligne :

1. Two lines if I have "Doctor" job and one of the other jobs for the same ID.
2. Only 1 line if :
   1. One single ID for any job (Ex : ID 002)
   2. Several lines for the same ID with either job category A (Doctor) or job category B (Nurse, NA, etc...) (but not both)

And I got this solution from @SamWiseOwl 

Filter measure =

var curId = SELECTEDVALUE('Nurse table'[Id]) --hold current id

var curJob = SELECTEDVALUE('Nurse table'[Job]) --hold current job

var curTime = SELECTEDVALUE('Nurse table'[Date]) -- hold current date

var People = CALCULATETABLE('Nurse table',ALLSELECTED('Nurse table'), 'Nurse table'[Id] = curId) --same id people

RETURN

SWITCH(

    TRUE()

    ,COUNTROWS(People) = 1, "Y" --Keep if one person

    ,maxx(Filter(People, 'Nurse table'[Job] <> "Doctor"), [Date])= curTime && curJob <> "Doctor"

    ,"Y" --Where this row is the biggest and not a doctor

    ,(maxx(Filter(People, 'Nurse table'[Job] = "Doctor"), [Date])= curTime && curJob = "Doctor"), "Y"--you are the max doctor

    ,"N"--anything else no

)

My problem becomes even more complex.

Now I'd like to add this condition: For each group [CurId x CurJob] if Max(Date) - Min(Date) > 1 hour then also keep the minx(Filter(People, 'Nurse table'[Job] <> "Doctor"), [Date])= minTime && curJob <> "Doctor" && diffTime >=1, "Y"

minx(Filter(People, 'Nurse table'[Job] = "Doctor"), [Date])= minTime && curJob = "Doctor" && diffTime >=1

[Anonymous · Accepted solution]

Hi @GOT2021 ,
Based on your description, you can try the following code to see if it's what you're expecting

Filter measure = 
VAR curId = SELECTEDVALUE('Nurse table'[Id])
VAR curJob = SELECTEDVALUE('Nurse table'[Job])
VAR curTime = SELECTEDVALUE('Nurse table'[Date])
VAR People = CALCULATETABLE('Nurse table', ALLSELECTED('Nurse table'), 'Nurse table'[Id] = curId)
VAR maxDate = MAXX(People, [Date])
VAR minDate = MINX(People, [Date])
VAR diffTime = DATEDIFF(minDate, maxDate, MINUTE)
RETURN
SWITCH(
    TRUE(),
    COUNTROWS(People) = 1, "Y",
    MAXX(FILTER(People, 'Nurse table'[Job] <> "Doctor"), [Date]) = curTime && curJob <> "Doctor", "Y",
    MAXX(FILTER(People, 'Nurse table'[Job] = "Doctor"), [Date]) = curTime && curJob = "Doctor", "Y",
    diffTime > 60 && MINX(FILTER(People, 'Nurse table'[Job] <> "Doctor"), [Date]) = curTime && curJob <> "Doctor", "Y",
    diffTime > 60 && MINX(FILTER(People, 'Nurse table'[Job] = "Doctor"), [Date]) = curTime && curJob = "Doctor", "Y",
    "N"
)

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[GOT2021]

Hi @Anonymous, Indeed, that's what I was expecting. Thank you so much 🙂