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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

You say you found a way to get the previous quarter. In my example I have a sequence number for the quarters with 1 starting at the earliest and 4 being the latest.

 

I also add 1 to each price, so you can see that you are getting the correct values.

 

You could create a statement like this to get the previous quarter value:


PreviousQuarterMaxPrice = VAR QuarterSequence =
    MAX ( Sheet1[QuarterSequence] )
RETURN
    CALCULATE (
        MAX ( Sheet1[Max_price] ),
        Sheet1[QuarterSequence]
            = QuarterSequence - 1,
        ALL ( Sheet1 )
    )

 

articleYearQuarterquarteryearMax_priceQuarterSequencePreviousQuarterMaxPrice
100268582017Qtr 2Qtr 220170419537
100268582017Qtr 1Qtr 1201719537319538
100268582016Qtr 3Qtr 3201619538219539
100268582016Qtr 1Qtr 12016195391 
  • NewbeePowerBI's avatar
    NewbeePowerBI
    Icon for Advocate I rankAdvocate I
    9 years ago

    Hi, Thank you for your solution.

     

    Removing  MAX from this MAX ( Sheet1[QuarterSequence] ) in your query gave me half the answer of my question.


    But I also have many articles like this in the column "article". How to add article condition into query? I want each article's value to be displayed

     

    e.g.

    articleyr&quaquarterMax_priseyearMax_Price_new
    100268582017Qtr 2Qtr 20201719537
    100268582017Qtr 1Qtr 119537201719537
    100268582016Qtr 3Qtr 319537201619537
    100268582016Qtr 1Qtr 119537201619537
    100267772016Qtr 2Qtr 20201610
    100267772016Qtr 1Qtr 1102016 
    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

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

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

        Hi Anonymous Using ALLExcept and adding condition for checking year, solved my problem.