topic Re: Properly handle missing value in dimension in App Development

Properly handle missing value in dimension

Tenuki — Fri, 01 Aug 2025 13:19:54 GMT

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 ?

Re: Properly handle missing value in dimension

Oleg_Troyansky — Fri, 01 Aug 2025 13:41:46 GMT

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

Re: Properly handle missing value in dimension

Kaushik2020 — Wed, 06 Aug 2025 04:25:43 GMT

try something like

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

Re: Properly handle missing value in dimension

Tenuki — Wed, 06 Aug 2025 06:53:59 GMT

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

Re: Properly handle missing value in dimension

howdash — Fri, 08 Aug 2025 15:43:11 GMT

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')