Forum Discussion

R_'s avatar
R_
Regular Visitor
8 years ago

Nested If condition

Hi All,

 

How to make nested if condition in Power BI.

 

I have to create condition as :

 

If (Amount is <=1000 and Year is MaxYear) ,

              If(Amount is between <1000 and Year = MaxYear-1) then "Period1" ,

               If(Amount is between <1000 & >2000 and Year = MaxYear-1) then "Period2"...

 

Sample :

 

Period= IF(Data[Amount]<=1000 && YEAR(Data[Date])=(MAX('Date'[Year]))),
                         IF(Data[Amount]<=1000 && Data[Amount]<2001 && YEAR(Data[Date])=(MAX('Date'[Year])-1)),"<1" endif BLANK()))))

 

 

 

Thanks,

R_

Need Help
Tips and Tricks

8 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    Hi R_,

     

    The best way is to use a SWITCH function something like this:

     

    Period =
SWITCH (
    TRUE (),
    SUM ( Table[Amount] ) <= 1000
        && Date[Year] = MAX ( Date[Year] ), "Period",
    SUM ( Table[Amount] ) < 1000
        && Date[Year]
            = MAX ( Date[Year] ) - 1, "Period1",
    SUM ( Table[Amount] ) < 1000
        && SUM ( Table[Amount] ) > 1000
        && Date[Year] = MAX ( Date[Year] ), "Period2",

   ...,
    "ALL PERIOD"
)

    Regards,

    MFelix

    • R_'s avatar
      R_
      Regular Visitor
      8 years ago

      Hi ,

       

      Period =
SWITCH (
    TRUE (),
    SUM ( Table[Amount] ) <= 1000
        && Date[Year] = MAX ( Date[Year] ), "Period",
    SUM ( Table[Amount] ) < 1000
        && Date[Year]
            = MAX ( Date[Year] ) - 1, "Period1",
    SUM ( Table[Amount] ) < 1000
        && SUM ( Table[Amount] ) > 1000
        && Date[Year] = MAX ( Date[Year] ), "Period2",

      Hi Plz chk my requirement once again..

       

      If (Amount is <=1000 and Year is MaxYear) ,

                    If(Amount is between <1000 and Year = MaxYear-1) then "Period1" ,

                     If(Amount is between <1000 & >2000 and Year = MaxYear-1) then "Period2"...

       

      If (Amount is <=1000 and Year is MaxYear)  ::: this is common for period1 & period2..

       

      Thanks,

  • bsas's avatar
    bsas
    Post Patron
    8 years ago

    Hi R_,

     

    Try this:

    IF(AND(Amount <=1000, date(Year) = Max(date(Year))),IF(and(Amount <1000, max(date(year)-1))),period1,   IF(and(Amount <1000, and(Amount >2000, date(Year) = max(date(year)-1)))),period2,blank())))

      • bsas's avatar
        bsas
        Post Patron
        8 years ago

        Hi R_

         

        In this case your requremens still are not clear for me. I've changed formula but based on yuor data only period1 can be reached or blank.

         

        Bucket = IF(and(Data[Amount] <= 1000, Data[year] = MAX(Data[year])-1),"period1",IF(and(Data[Amount] <= 1000, and(Data[Amount] >= 2000, Data[year] = MAX(Data[year])-1)),"period2",BLANK()))