Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
R_
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
bsas
Post Patron
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())))

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

 

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


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

Proud to be a Super User!

Check out my blog: Power BI em Português



R_
Regular Visitor

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_

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


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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
Super User
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


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

Proud to be a Super User!

Check out my blog: Power BI em Português



R_
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,

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.