Forum Discussion
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 | 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 |
Being new to the DAX language, I must admit I'm at a loss to get the table below
I want to show this
| Date | Id | User | Job |
10/01/2024 15:01:24 | 001 | Adam | 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 |
| 17/02/2024 15:26:00 | 003 | Patrick | Doctor |
| 25/03/2024 14:00:00 | 004 | George | Doctor |
| 26/03/2024 14:15:00 | 005 | Sophie |
For each ID I would like to keep at most 2 ligne :
- Two lines if I have "Doctor" job and one of the other jobs for the same ID.
- Only 1 line if :
- One single ID for any job (Ex : ID 002)
- Several lines for the same ID with either job category A (Doctor) or job category B (Nurse, NA, etc...) (but not both)
I'd thought of creating an incremental measure 1, 2,3 .... for job category B and a, b, c... for job category A so I can filter to keep only 1 and/or a but I'm not up to that either (:
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)
3 Replies
- SamWiseOwl
Super User
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)Thank you SamWiseOwl, it's exactly what I was looking for, it answers my problem 🙂
- SamWiseOwl
It was a fun problem to solve.
There were other versions but this was the most efficient that I could think of!
