cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
R_ Frequent Visitor
Frequent Visitor

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_

8 REPLIES 8
Highlighted
Super User
Super User

Re: Nested If condition

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




R_ Frequent Visitor
Frequent Visitor

Re: Nested If condition


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 Member
Member

Re: Nested If condition

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_ Frequent Visitor
Frequent Visitor

Re: Nested If condition

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_

 

bsas Member
Member

Re: Nested If condition

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

Super User
Super User

Re: Nested If condition

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




R_ Frequent Visitor
Frequent Visitor

Re: Nested If condition

Hi @MFelix ,

 

Let me explain once again.

 

Let we have 3 bucket for 2017 condition:

<=1000

1000-2000

>3000

 

 

11Capture.JPGI 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_

Super User
Super User

Re: Nested If condition

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!