5 Replies Latest reply on Aug 22, 2016 6:53 AM by Sabine Honer

# How to create a range from Null - 5?

Hi All,

I hope you are doing fine.

I need to create a filter, that uses a range.

Issue here is, that Null values should be also included.

If I have values in dimensions, I can only pick one value, but I need to select a range

If I use values in measures, I can use a range without Nulls.

I could create a helper column, where I assign -1 for Nulls.

Is there a way to display "Null" in filter instead of -1?

Please find below workbook ( version 9.0; as I have also 9.2 and 9.3 installed please feel free to upgrade it if needed).

Best regards

Sabine

• ###### 1. Re: How to create a range from Null - 5?

Hi.

In default behavior Null can be in your presentation but not in your filter.

You can do this way:

Null Values --> If ISNULL( SUM( [Val] ) ) Then 'Null' Else 'Not Null' End

1 of 1 people found this helpful
• ###### 2. Re: How to create a range from Null - 5?

Sabine;

Looks to me like you want your range to say "NULL" rather than -1?

Since you're asking specifically to create ranges, I think you're going to have some challenges. Null doesn't fall on the number line, so one can't include it in a range (which is why you made Val Helper!)

I think you might be able to "hack" this and get some of the interactivity of a range by hiding the -1 with a floating container.

The floating container is just a calculation showing:

*Make sure Val Helper field is shared between all your sheets.

This is just a hack, though, to cover up the -1 with "NULL." It's not very pretty, and the user won't be able to click this box and set a new value (he/she will need to use the slider).

Maybe you need to think of anther route, like a key for the user showing -1 = NULL? I'll be watching closely to see if someone else can crack this! Good luck!

1 of 1 people found this helpful
• ###### 3. Re: How to create a range from Null - 5?

Hi Luciano and Michael,

They helped me a lot as I know now, that there isn't just an easy answer or workaround :-)

Thank you very much again for the quick answers.

Have a great day

Sabine

• ###### 4. Re: How to create a range from Null - 5?

I gave you the workaround. I believe to be a prettier way than show -1.

Good lucky.

1 of 1 people found this helpful
• ###### 5. Re: How to create a range from Null - 5?

Yes, you both gave me a workaround and I am really happy with them.

Let's see which one my user will go :-)