cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular 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
Post Patron

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

Regular Visitor

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_

Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Regular Visitor

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_

Super User

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Post Patron

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

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Regular Visitor

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,

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.