Forum Discussion
zero value to be calculated
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
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 )
) )))))
4 Replies
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 )
) )))))
Davehus,
Thank you for your quick response, it works seemesly. Have a great week.
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
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
