Forum Discussion
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...
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 idvar curJob = SELECTEDVALUE('Nurse table'[Job]) --hold current jobvar curTime = SELECTEDVALUE('Nurse table'[Date]) -- hold current datevar People = CALCULATETABLE('Nurse table',ALLSELECTED('Nurse table'), 'Nurse table'[Id] = curId) --same id peopleRETURNSWITCH(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)
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
)
Thank you SamWiseOwl, it's exactly what I was looking for, it answers my problem 🙂
It was a fun problem to solve.
There were other versions but this was the most efficient that I could think of!
