Forum Discussion

GOT2021's avatar
GOT2021
Frequent Visitor
1 year ago
Solved

Calculate DateTime Diff In Group BY with DAX

Hi, my problem was this : 

DateIdUserJob

10/01/2024 15:01:24

001AdamNurse
10/01/2024 15:11:24001MaxNurse
10/01/2024 16:01:24001GeorgeDoctor
15/01/2024 08:00:00002MichelNurse
17/02/2024 17:00:50003MaxNurse
1702/2024 14:10:00003GeorgeDoctor
17/02/2024 15:26:00003PatrickDoctor
25/03/2024 14:00:00004GeorgeDocor
26/03/2024 12:25:00005AnneNurse
26/03/2024 14:15:00005Sophie 

 

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

General Comment
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      GOT2021
      Frequent Visitor
      1 year ago

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