Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
RavirajMSE
Frequent Visitor

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 NoDateShiftShift type
B926/11/20223Latest Shift
B1026/11/20223Latest Shift
B1126/11/20223Latest Shift
B1226/11/20223Latest Shift
B1326/11/20223Latest Shift
A125/11/20221Not the latest shift
A225/11/20221Not the latest shift
A325/11/20221Not the latest shift
A425/11/20221Not the latest shift
A525/11/20222Not the latest shift
A625/11/20222Not the latest shift
A725/11/20222Not the latest shift
A825/11/20222Not the latest shift
A925/11/20223Not the latest shift
A1025/11/20223Not the latest shift
A1125/11/20223Not the latest shift
A1225/11/20223Not the latest shift
A1325/11/20223Not the latest shift
A126/11/20221Not the latest shift
A226/11/20221Not the latest shift
A326/11/20221Not the latest shift
A426/11/20221Not the latest shift
A526/11/20222Not the latest shift
A626/11/20222Not the latest shift
B726/11/20222Not the latest shift
B826/11/20222Not the latest shift
1 ACCEPTED SOLUTION
mangaus1111
Solution Sage
Solution Sage

Hi @RavirajMSE ,

you can try this measure

mangaus1111_0-1669388197485.png

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.

View solution in original post

3 REPLIES 3
mangaus1111
Solution Sage
Solution Sage

Hi @RavirajMSE ,

you can try this measure

mangaus1111_0-1669388197485.png

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.

RavirajMSE
Frequent Visitor

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 

RavirajMSE
Frequent Visitor

Adding the table once again, above one seems confusing

Pot NoDate ShiftShift type
B926/11/2022 3Latest Shift
B1026/11/2022 3Latest Shift
B1126/11/2022 3Latest Shift
B1226/11/2022 3Latest Shift
B1326/11/2022 3Latest Shift
A125/11/2022 1Not the latest shift
A225/11/2022 1Not the latest shift
A325/11/2022 1Not the latest shift
A425/11/2022 1Not the latest shift
A525/11/2022 2Not the latest shift
A625/11/2022 2Not the latest shift
A725/11/2022 2Not the latest shift
A825/11/2022 2Not the latest shift
A925/11/2022 3Not the latest shift
A1025/11/2022 3Not the latest shift
A1125/11/2022 3Not the latest shift
A1225/11/2022 3Not the latest shift
A1325/11/2022 3Not the latest shift
A126/11/2022 1Not the latest shift
A226/11/2022 1Not the latest shift
A326/11/2022 1Not the latest shift
A426/11/2022 1Not the latest shift
A526/11/2022 2Not the latest shift
A626/11/2022 2Not the latest shift
B726/11/2022 2Not the latest shift
B826/11/2022 2Not the latest shift

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.