Forum Discussion

Sarah--Flex's avatar
Sarah--Flex
Frequent Visitor
7 years ago
Solved

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,...
Need Help
  • davehus's avatar
    davehus
    7 years ago

    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's avatar
Sarah--Flex
Frequent Visitor
7 years ago

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