If column is blank, return blank in calculate member

Questions & Answers
1

I have the following calculated member for Budget +/-:

[Measures].[Budget] - [Measures].[Budget Spent]

Because my report rows include “Logged by” underneath “Issue” there is only a budget in the Issues level as expected but I would like the “Budget +/-” to show blank if the “Budget” column is blank. Can I update my calculated member for “Budget +/-” to do that?

image
image601×243 9.84 KB

2

Hi @Sdemidow!

maybe, instead of that simple calculation, try to use a condition saying that - calculate it only if Budget has any value, and if not (if it’s empty) then return blank :slight_smile:

CASE
WHEN [Measures].[Budget] > 0
THEN
[Measures].[Budget] -
[Measures].[Budget Spent]
ELSE
“”
END

3

Hello @LauraHilkovic ,

Great suggestion!

However, I would suggest removing the ELSE branch that returns the empty string.
If there are no instructions probvided, the ELSE branch returns null, which is totally blank without any value.

Also, different data formats - value and string - might cause errors if measure values are used in further calculations.

The adjusted expression might then be as follows.

CASE WHEN
 [Measures].[Budget] > 0
THEN
 [Measures].[Budget] 
-
 [Measures].[Budget Spent]
END

Regards,
Oskars/ support@eazyBI.com

1 Like