Nested If condition

[R_]

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_

[bsas]

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())))

[R_]

Hi @bsas @MFelix,

I have created period logic but if not showing elements. PF pbix file for details.

https://www.dropbox.com/s/uji1sv4dcfuprhv/period.pbix?dl=0

Thanks,

R_

[MFelix]

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_]

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_

[MFelix]

Hi @R_,

Just to help you better, the questions that you make are the ones that have to be answered in order to have your formula.

You want to have buckets by year and the 3 buckets or only by the 3 buckets? 

As I said if you can post in your image the expected result for each line it would be easier to help you, sorry for insisting on this but still not clear on what are the requirements of your question.

regards,

MFelix

[bsas]

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]

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_]

---

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,