Forum Discussion
How to apply max and if condition on multiple column?
Look at this table,
Suppose I have given only three column Pot. No, Date, Shift.
And this data gets appended every eight hours so you can see there are three shifts given over there so here I have to create the last column which is shift type, And in shift type I have to write dynamic dax, dynamic dax basically means the last shift would be latest shift. so first shift is shift 1 , second is shift 2 and 3rd is three respective to date.
I have written this dax but it doesn't work
Shift type = if(Fact_Table[date] = MAX(Fact_Table[date]),IF(Fact_Table[time - Copy.2 - Copy]=max(Fact_Table[time - Copy.2 - Copy]),"Latest Shift","Not the latest Shift")
please help
amitchandak @Greg_Deckler
| Pot No | Date | Shift | Shift type |
| B9 | 26/11/2022 | 3 | Latest Shift |
| B10 | 26/11/2022 | 3 | Latest Shift |
| B11 | 26/11/2022 | 3 | Latest Shift |
| B12 | 26/11/2022 | 3 | Latest Shift |
| B13 | 26/11/2022 | 3 | Latest Shift |
| A1 | 25/11/2022 | 1 | Not the latest shift |
| A2 | 25/11/2022 | 1 | Not the latest shift |
| A3 | 25/11/2022 | 1 | Not the latest shift |
| A4 | 25/11/2022 | 1 | Not the latest shift |
| A5 | 25/11/2022 | 2 | Not the latest shift |
| A6 | 25/11/2022 | 2 | Not the latest shift |
| A7 | 25/11/2022 | 2 | Not the latest shift |
| A8 | 25/11/2022 | 2 | Not the latest shift |
| A9 | 25/11/2022 | 3 | Not the latest shift |
| A10 | 25/11/2022 | 3 | Not the latest shift |
| A11 | 25/11/2022 | 3 | Not the latest shift |
| A12 | 25/11/2022 | 3 | Not the latest shift |
| A13 | 25/11/2022 | 3 | Not the latest shift |
| A1 | 26/11/2022 | 1 | Not the latest shift |
| A2 | 26/11/2022 | 1 | Not the latest shift |
| A3 | 26/11/2022 | 1 | Not the latest shift |
| A4 | 26/11/2022 | 1 | Not the latest shift |
| A5 | 26/11/2022 | 2 | Not the latest shift |
| A6 | 26/11/2022 | 2 | Not the latest shift |
| B7 | 26/11/2022 | 2 | Not the latest shift |
| B8 | 26/11/2022 | 2 | Not the latest shift |
Hi RavirajMSE ,
you can try this measure
Measure = VAR MaxDate= CALCULATE(MAX('Table'[Date]),ALL('Table')) VAR MaxShift = CALCULATE(MAX('Table'[Shift]),ALL('Table')) RETURN SWITCH(TRUE(), SELECTEDVALUE('Table'[Date]) = MaxDate && SELECTEDVALUE('Table'[Shift]) = MaxShift, "Latest Shift", "Not the latest Shift" )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Adding the table once again, above one seems confusing
Pot No Date Shift Shift type B9 26/11/2022 3 Latest Shift B10 26/11/2022 3 Latest Shift B11 26/11/2022 3 Latest Shift B12 26/11/2022 3 Latest Shift B13 26/11/2022 3 Latest Shift A1 25/11/2022 1 Not the latest shift A2 25/11/2022 1 Not the latest shift A3 25/11/2022 1 Not the latest shift A4 25/11/2022 1 Not the latest shift A5 25/11/2022 2 Not the latest shift A6 25/11/2022 2 Not the latest shift A7 25/11/2022 2 Not the latest shift A8 25/11/2022 2 Not the latest shift A9 25/11/2022 3 Not the latest shift A10 25/11/2022 3 Not the latest shift A11 25/11/2022 3 Not the latest shift A12 25/11/2022 3 Not the latest shift A13 25/11/2022 3 Not the latest shift A1 26/11/2022 1 Not the latest shift A2 26/11/2022 1 Not the latest shift A3 26/11/2022 1 Not the latest shift A4 26/11/2022 1 Not the latest shift A5 26/11/2022 2 Not the latest shift A6 26/11/2022 2 Not the latest shift B7 26/11/2022 2 Not the latest shift B8 26/11/2022 2 Not the latest shift Shift type = if(Fact_Table[date] = MAX(Fact_Table[date])&&IF(Fact_Table[shift]=MAX(Fact_Table[shift]),"latest shift","not the latest shift"))
This one also doesn't work
amitchandak Greg_Deckler Jihwan_Kim Navneet_Kaur mangaus1111 Mikelytics
- mangaus1111
Solution Sage
Hi RavirajMSE ,
you can try this measure
Measure = VAR MaxDate= CALCULATE(MAX('Table'[Date]),ALL('Table')) VAR MaxShift = CALCULATE(MAX('Table'[Shift]),ALL('Table')) RETURN SWITCH(TRUE(), SELECTEDVALUE('Table'[Date]) = MaxDate && SELECTEDVALUE('Table'[Shift]) = MaxShift, "Latest Shift", "Not the latest Shift" )If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
