cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
snandy2011 Member
Member

Nested If / And statement not working

Hi All,

I know, this is something relatively easy, but i cannot figure out my issues with this DAX . Below is my scenario..

 

I have Sales measure which gives me total sales of selected year in Slicer.

I also have Previous year sales measure which gives me Last year sales.

 

Based on that, I am calcuating growth rate, which is also in another measure.

 

My growth rate formula look like,

 

DIVIDE('Measure Dimension for Product'[Measure Selection for Product]-[Last Year],[Last Year])

 

format is Percentage..

Now, i am segemnting this growth rate by different category like 1,2,3

 for example,

if the growth rate is 0 to 25 , then 1

if the growth rate is 26 to 50 , then 2

if the growth rate is greater than 100,3

else,0

 

for that, logic i have written below formula,

 

IF(
    AND([Growth Rate By Year]>0,[Growth Rate By Year]<=25),"1",IF(AND([Growth Rate By Year]>=26,[Growth Rate By Year]<=50),"2","0" ) )

 

Does not work.. It gives me only 1 or 0.. when growth rate is also 26

to 50.. It shows also 1.

 I have tried with Switch statement also,

 

SWITCH(TRUE(),
[Growth Rate By Year] >= 0 && [Growth Rate By Year] <=25,"1",
[Growth Rate By Year]>=26 && [Growth Rate By Year] <=50,"2",
"0")
 
But same result..
Can you please identify what wrong in this formula..??
 
I am not able to find out any issues.But still it is not showing the correct result.
 
Any suggesation is really appreciable.
 
Thanks,
snandy
 
 
 
 
 
 
 
 
 
 
 
 
 
1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Nested If / And statement not working

Hi @snandy2011

It could be that your measure is yielding results different from what you expect.

In any case, try using 0,25 instead of 25, 0,50 instead of 50, etc. Those are the real values of your division even though you are formatting them as percentage.

View solution in original post

5 REPLIES 5
Super User III
Super User III

Re: Nested If / And statement not working

Hi @snandy2011

It could be that your measure is yielding results different from what you expect.

In any case, try using 0,25 instead of 25, 0,50 instead of 50, etc. Those are the real values of your division even though you are formatting them as percentage.

View solution in original post

yelsherif Member
Member

Re: Nested If / And statement not working

Seems you meant to categorize a percentage, .e.g. 0-25% , 25%-50%.

Your formula checks integer values, not percentages.

On the other hand, your formula does not include values above 50 which may be the case that actual values are above 50 (or 50%) and is not recognized, so the formula returns 0

Can you provide a sample data to check it please

Highlighted
ryan_mayu Established Member
Established Member

Re: Nested If / And statement not working

@snandy2011

 

I am afraid the growth rate  is between 25 and 26. Depends on your coding logic, it will also show as 0 when your value is between 25 and 26. Please show the value with one decimal number and double check.

 

You can try modify the coding like below.

IF(
    AND([Growth Rate By Year]>0,[Growth Rate By Year]<=25),"1",IF(AND([Growth Rate By Year]>25,[Growth Rate By Year]<=50),"2","0" ) )
 
I have done the test, it works on my end. Thanks.
c1.JPG
 
snandy2011 Member
Member

Re: Nested If / And statement not working

@ryan_mayu@yelsherif @AlB,

 

I apologize to everybody for asking this stupid question. I should have sticked with that issues and sloved by myself.Actually, I lost patience.. After some hours, i found out the actual reason when i did it on Excel. The issues was that, i changed the format of that measures. and within if function i was using only integer number, i should have used decimal number.when i did it on excel it came into my mind..

 

Thanks to everyone, for your reply and your valueable time..and sorry once again for asking this stupid type question.

 

Thanks,

snandy

 

 

Super User III
Super User III

Re: Nested If / And statement not working

@snandy2011

No worries. We've all been in similar situations Smiley Happy

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors