Expression for Labeling and Selecting Null Values

[ltc]

Hi,

I have a column name "CCaR User DD2875 is Valid" which has Yes, No, and Null values.  I'd like to label the null values as "Empty" and make the null values selectable.  What is the expression for this?

Thank you

[adityaakshaya]

Hi Lea,

Please use the below expression in your Dimension :  Field, it will work for you.

if([CCaR User DD2875 is Valid]='','Empty',[CCaR User DD2875 is Valid]) and in the label, you can name it as per the requirement.

Regards,

Akshaya

PS- if you find response as correct and Helpful, Please mark it as correct and helpful

[ltc]

Thank you for the response!  Unfortunately, it didn't seem to work.  The null values are still appearing as "-".

any suggestions?

[m_perreault · Accepted solution]

If(isnull([CCaR User DD2875 is Valid]),'Empty',[CCaR User DD2875 is Valid])

[adityaakshaya]

You can try like below

if(isnull([CCaR User DD2875 is Valid]),'Empty',[CCaR User DD2875 is Valid])

or

if(Len([CCaR User DD2875 is Valid])=0,'Empty',[CCaR User DD2875 is Valid])

Regards,

Akshaya

[ltc]

Thank you!

It did label the null values as Empty and I am able to select the "Empty" label on the donut chart.

How can I get the null values to also be included in the KPI chart?  Without filters selected, it should show 13607 of 13607 users.  However, it shows 11,050 because it's not counting the null values.

Here is my current KPI expression.  It's just a count.

But I need it to include null values.  What would the expression be?

Thank you!

[ltc]

Thank you so much.  It worked, but there was another person who replied before you and I used his expression.  I wish I could mark both as correct.  If there is a way to do so, please let me know.

[m_perreault]

My recommendation would be to move this logic to your script

If(isnull([CCaR User DD2875 is Valid]),'Empty',[CCaR User DD2875 is Valid]) as [CCar User DD2875 is Valid]

[ltc]

Makes sense!  Although I am not the owner of the app, so I can't open the data load editor.

I am just working on a sheet for myself, so I don't think I have a choice but to edit the KPI expression.

Is there a way to edit my current KPI expression to include null values?

My current expression is a count:

Count([CCaR User DD2875 Is Valid])

I don't know how to add an IF statement if I have a count.

Thank you again.

[vishsaggi]

Try this?

= Sum(Aggr(Count(If(isnull([CCaR User DD2875 is Valid]),1,[CCaR User DD2875 is Valid])), [CCaR User DD2875 is Valid]))