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 = MaxYe...
Need Help
Tips and Tricks
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()))

    • MFelix's avatar
      MFelix
      Super User
      8 years ago

      Hi R_,

       

      As R_ I also don't understand your requirements, can you please post an example of the final result you need and also the requirements ni text not with IF formulas.

       

      Regards,

      MFelix

      • R_'s avatar
        R_
        Regular Visitor
        8 years ago

        Hi MFelix ,

         

        Let me explain once again.

         

        Let we have 3 bucket for 2017 condition:

        <=1000

        1000-2000

        >3000

         

         

        I have to find out the count of Employee which has <=1000 salary in last year (2016) :: We have 2 Emp(highlighted in bold for 2016) which comes under 1000 bucket. 

         

        So we can create bucket for 2016 as:

        IF(AND(Amount <=1000, Year = (Max(Year)-1),”<=1000”,Blank())

         

        Now Question comes that in which bucket these 2 emp(highlighted in bold for 2017)  are belong to?

        So how to write if condition for 2017 bucket. I have tried but below condition is not working..

         

        IF(AND(Amount <=1000, date(Year) = Max(date(Year))),

                                      IF(and(Amount <1000, max(date(year)-1))),">1000",Blank()))

         

        Regards,

        R_