Forum Discussion

NewbeePowerBI's avatar
NewbeePowerBI
Icon for Advocate I rankAdvocate I
9 years ago
Solved

How to use if else with two conditions using DAX

Hi, I am new to Power BI and I have data like this in sales table. I want to create a new column Max_price_new where 0 should be replaced by the Max_price of earlier quarter.   e.g. This is the in...
  • Anonymous's avatar
    Anonymous
    9 years ago

    Try using  ALLEXCEPT ( Sheet1[Article] ) instead of  ALL ( Sheet1 ).

  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi NewbeePowerBI,

     

    Please refer to below calculated columns:

    Quarter Num = RIGHT(Sheet1[quarter],1)

Last quarter =
IF (
    Sheet1[Max_prise] = 0,
    CALCULATE (
        MAX ( Sheet1[Quarter Num] ),
        FILTER (
            ALLEXCEPT ( Sheet1, Sheet1[article], Sheet1[year] ),
            Sheet1[Quarter Num] < EARLIER ( Sheet1[Quarter Num] )
        )
    ),
    Sheet1[Quarter Num]
)

Max_prise New =
LOOKUPVALUE (
    Sheet1[Max_prise],
    Sheet1[article], Sheet1[article],
    Sheet1[year], Sheet1[year],
    Sheet1[Quarter Num], Sheet1[Last quarter]
)

     

    Best regards,
    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi NewbeePowerBI,

 

Please refer to below calculated columns:

Quarter Num = RIGHT(Sheet1[quarter],1)

Last quarter =
IF (
    Sheet1[Max_prise] = 0,
    CALCULATE (
        MAX ( Sheet1[Quarter Num] ),
        FILTER (
            ALLEXCEPT ( Sheet1, Sheet1[article], Sheet1[year] ),
            Sheet1[Quarter Num] < EARLIER ( Sheet1[Quarter Num] )
        )
    ),
    Sheet1[Quarter Num]
)

Max_prise New =
LOOKUPVALUE (
    Sheet1[Max_prise],
    Sheet1[article], Sheet1[article],
    Sheet1[year], Sheet1[year],
    Sheet1[Quarter Num], Sheet1[Last quarter]
)

 

Best regards,
Yuliana Gu