Forum Discussion

GOT2021's avatar
GOT2021
Frequent Visitor
1 year ago
Solved

Dax Formula Group By multiple conditions

Hi, I have this data 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...
General Comment
Need Help
Tips and Tricks
  • SamWiseOwl's avatar
    SamWiseOwl
    1 year ago

    HI GOT2021 
    Create a measure and add it into the Filter pane under Filters on this visual.

    Then filter to Y.

     

    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
    )



     

GOT2021's avatar
GOT2021
Frequent Visitor
1 year ago

Thank you SamWiseOwl, it's exactly what I was looking for, it answers my problem 🙂

SamWiseOwl's avatar
SamWiseOwl
Super User
1 year ago

It was a fun problem to solve.

There were other versions but this was the most efficient that I could think of!