Properly handle missing value in dimension

[Tenuki]

Hello, 

I am trying to create a condition a null field for the field "Key Confirm".

This is my mesure :

=IF(ISNULL(Key_Confirm), 'Not Confirmed', 'Confirmed')
But for null key_confirm, it doesn't work, and only display a empty field with '-'.

What I am missing ? 

[Oleg_Troyansky]

Hi there,

There are two possible reasons for this issue:

1. Depending on the makeup of your chart dimensions, there might be multiple values of Key_Confirm available for a given cell. When multiple values are available, the condition will never be evaluated, and a dash '-' is displayed instead.

2. It's possible that the value is not null, but it's missing, for the combination of the chart Dimension. There is a difference between the NULL value and a missing value. Missing values will not be evaluated as nulls, and they are displayed the same way - with a dash '-'.

Cheers,

Oleg Troyansky

[Kaushik2020]

try something like 

=IF(Key_Confirm = '-', 'Not Confirmed', 'Confirmed')

[Tenuki · Accepted solution]

I have try, but it didn't work with my model.

I have found a work around with mapping 

Mymap:

Mapping LOAD 
OrderLineID, 'Confirmed'
FROM Confirmation

Mytable:
OrderLineID, ApplyMap('Mymap', Confirmation_ID, 'Not Confirmed') as Confirmation_Status
FROM OrderLineTable

[howdash]

IsNull() function is...tricky. I prefer to use a combination of Len() and Trim() functions instead, like this:

If(Len(Trim(Key_Confirm)) = 0, 'Not Confirmed', 'Confirmed')

This considers both the null and empty cells.

Although, in your case, like @Oleg_Troyansky mentioned, your expression may be evaluating multiple values in a chart which will result in you seeing a '-' instead of either 'Not Confirmed' or 'Confirmed' text.

To handle scenario with an expression generating multiple values for evaluation, I recommend using the Concat() function, like this:

If(Len(Trim(Concat(Key_Confirm))) = 0, 'Not Confirmed', 'Confirmed')