How to remove the null option from control drop down

Question & Answer
, ,
1

Hi everyone,

I had an issue with missing dates in my dataset. To address this, I created a calendar table in my database and performed a right join with the calendar table to fill in any missing dates.

Although this resolved my initial issue, I’m now encountering a new problem. The control dropdowns are displaying “null” as an option.

I want to remove the “null” option from the control dropdowns without losing the functionality of filling in missing dates using the calendar table.

Can someone please help me with this?

2

Hi @achourey,

Based on your description of the problem it appears you are using the field from the dataset for the parameter control. But is it the date field from the Calendar table or the field from your data table?

Regards,
Giri

3

Hi @achourey,
Can you also post a sample of your dataset here? And does it contain any Nulls?
Thanks.

4

Fact Table

Activity Type Amount Source Date
Accrual 100 Web 01-01-2024
Accrual 10 Web 01-02-2024
Accrual 2 Mobile 01-03-2024
Accrual 11 Web 01-06-2024
Accrual 234 Web 01-08-2024
Accrual 45 Mobile 01-09-2024
Accrual 100 Pos 01-10-2024
Accrual 100 Web 01-11-2024

Calendar Table :

Date
01-01-2024
01-02-2024
01-03-2024
01-04-2024
01-05-2024
01-06-2024
01-07-2024
01-08-2024
01-09-2024
01-10-2024
01-11-2024

To get the missing date we have performed the right join between fact table and the calendar table. so we got all the missing dates.

Activity Type Amount Source Date
Accrual 100 Web 01-01-2024
Accrual 10 Web 01-02-2024
Accrual 2 Mobile 01-03-2024
null null null 01-04-2024
null null null 01-05-2024
Accrual 11 Web 01-06-2024
null null null 01-07-2024
Accrual 234 Web 01-08-2024
Accrual 45 Mobile 01-09-2024
Accrual 100 Pos 01-10-2024
Accrual 100 Web 01-11-2024

Currently, we are encountering null values for all the missing dates in the other column. As a result, if I attempt to use activity type or source as a control, I observe null in the drop-down value, and I prefer not to display that option.

5

Hi @achourey,

There are two possible options:

  1. If your Activity Type and Source have limited values that do not change; you could build your parameter as a static list of values, that match in content with your Fact table. This way you exclude Null from the list
  2. Alternatively, if you have dimension table for Activity Type and Source as well you could basically do a similar join with the factor table as you did with calendar table and use the columns from the dimension tables for the parameter.
6

Here are the steps to exclude null values:

  1. Once you created a filter on the column (Say ‘Activity Type’ in this case), click on the edit filter.
  2. Set Filter Type = ‘Custom filter list’ and let the list stay empty.
  3. set Null Options = ‘Exclude nulls’ and hit Apply.
  4. You wont be seeing Nulls in your dropdown list for that column.
    image
    image642×778 41.6 KB

Its not the best solution but rather a hack to get around. Let us know if this helps!
Thanks!

1 Like