Forum Discussion
Returning formula from ISBlank
Hi there,
Sorry if this has been answered elsewhere but couldn't see it.
In other examples, I've seen people returning an individual value for the relevant true/false output from an 'ISBLANK' statement i.e
```
Count1_formatted = if (isblank( '261use 2021 Summary'[Count1]), 0 ,1 )
```
I was wondering if it was possible to instead of returning a 1 if False to instead run a calculation such as:
```
Count1_formatted = if (isblank( '261use 2021 Summary (2)'[Count1]), 0 , (('261use 2021 Summary (2)'[Time Spent (manual)] - '261use 2021 Summary (2)'[Time Spent (Dynamo)]) / 60) / 60 ).
```
This currently isn't throwing any errors but is simply only returning a '0' for every cell. I know there are cells where False occurs as when I replace the False value formula with a 1 there are cells containing a 1.
Any help is appreciated!
Hi Anonymous ，
I think I know why you are getting values of 0. First of all the data type of Count1_formatted is set to Whole number and if '261use 2021 Summary (2)'[Count1] is not blank value when the expression (( '261use 2021 Summary (2)'[ Time Spent (Manual)] - '261use 2021 Summary (2)'[Time Spent (Dynamo)] ) / 60 ) / 60 returns a decimal number with decimal places between 0 and 1. But its data type is set to whole number, so it ends up returning 0.
You can set the data type of Count1_formatted to Decimal Number, with 4 or more decimal places just as below screenshot.
Count1_formatted = IF ( ISBLANK ( '261use 2021 Summary (2)'[Count1] ), BLANK(), ( ( '261use 2021 Summary (2)'[Time Spent (manual)] - '261use 2021 Summary (2)'[Time Spent (Dynamo)] ) / 60 ) / 60 )Best Regards
5 Replies
Samarth_18Community Champion
Hi Anonymous
Your formula looks correct, can you try to check your data what exactly coming in this column
"261use 2021 Summary (2)'[Time Spent (manual)]" & "261use 2021 Summary (2)'[Time Spent (Dynamo)])" and try to do manual calculation.Or is it possible for you to share your PBIX file after removing sensitve data?
Hi Anonymous ,
According to your description, it seems that the condition isblank( '261use 2021 Summary (2)'[Count1]) always returns True, so all columns are displayed as 0. Is Count1 a fact field in the table or a measure? If it is a measure, could you please provide the formula for it? If not, could you please share some sample data in the table '261use 2021 Summary (2)'(exclude sensitive data) in order to make troubleshooting and provide you a suitable solution? It is better if you can provide a simplified pbix file without sensitive info. Thank you.
Best Regards
Thanks for the response,
The Count1 is a field coming from my datasource and not a measure.
Please see the pbix file in the WE Transfer link below- The column which the formula is required in is the Count1_formatted field.
Many thanks,
Jake
Hi Anonymous ，
I think I know why you are getting values of 0. First of all the data type of Count1_formatted is set to Whole number and if '261use 2021 Summary (2)'[Count1] is not blank value when the expression (( '261use 2021 Summary (2)'[ Time Spent (Manual)] - '261use 2021 Summary (2)'[Time Spent (Dynamo)] ) / 60 ) / 60 returns a decimal number with decimal places between 0 and 1. But its data type is set to whole number, so it ends up returning 0.
You can set the data type of Count1_formatted to Decimal Number, with 4 or more decimal places just as below screenshot.
Count1_formatted = IF ( ISBLANK ( '261use 2021 Summary (2)'[Count1] ), BLANK(), ( ( '261use 2021 Summary (2)'[Time Spent (manual)] - '261use 2021 Summary (2)'[Time Spent (Dynamo)] ) / 60 ) / 60 )Best Regards