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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors