zero value to be calculated

[Sarah--Flex]

Hi all,

I've been using Power Bi for a few months and so far i love it... but i have an issue on a calculation and i cant figure it out.  i hope someone has the answer.

I have values in Zero, and I am using a IF calculation to transform 0 into 5, but so far is not working, and leaves the result as blank. I have tried 2 ways

Points MCARs = IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=BLANK()),BLANK(),IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]>6),0,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=5),1,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=4),2,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=3),3,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=2),4,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=1),5,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=0),5,0))))))))

and this

Points MCARs = IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=BLANK()),BLANK(),IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]>6),0,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=5),1,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=4),2,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=3),3,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=2),4,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]<=1),5,0)))))))

it calculates all of the conditional except the zero.   (see the last IF calculation in the formula)

what am i doing wrong?

thanks for your help

Sarah

[Sarah--Flex]

hello all,

I'm very new in Power Bi... so far i love it.   but i need your help with.   My data base has values as zero, and i need it to be taken into consideration as a value so when I run my IF calculation gives me a result of 5 points.  so far the value ZERO is not translated into the 5 that i need.   it keeps the result as blank

so far i have tried this:

Points MCARs = IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=BLANK()),BLANK(),IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]>6),0,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=5),1,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=4),2,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=3),3,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=2),4,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]<=1),5,0)))))))

and this

Points MCARs = IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=BLANK()),BLANK(),IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]>6),0,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=5),1,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=4),2,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=3),3,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=2),4,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=1),5,IF(AND('Raw Data'[Metric]="MCARs",'Raw Data'[Score]=0),5,0))))))))

hope you can help.   thanks

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[davehus · Accepted solution]

Hi, I've change the code slightly and it should work now. Where you are declaring the blank, try to wrap it with an ISBLANK formula as below.

HTH 

D

Please mark as a solution if you are happy with the result.

=
IF (
 AND ( 'Raw Data'[Metric] = "MCARs", ISBLANK('Raw Data'[Score])),
 BLANK (),
 IF (
 AND ( 'Raw Data'[Metric] = "MCARs", 'Raw Data'[Score] > 6 ),
 0,
 IF (
 AND ( 'Raw Data'[Metric] = "MCARs", 'Raw Data'[Score] = 5 ),
 1,
 IF (
 AND ( 'Raw Data'[Metric] = "MCARs", 'Raw Data'[Score] = 4 ),
 2,
 IF (
 AND ( 'Raw Data'[Metric] = "MCARs", 'Raw Data'[Score] = 3 ),
 3,
 IF (
 AND ( 'Raw Data'[Metric] = "MCARs", 'Raw Data'[Score] = 2 ),
 4,
 IF ( AND ( 'Raw Data'[Metric] = "MCARs", 'Raw Data'[Score] <= 1 ), 5, 0 )
 ) )))))

[Sarah--Flex]

Davehus,

Thank you for your quick response, it works seemesly.  Have a great week.