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.
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?
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.
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
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.
Hi @achourey,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post?
If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.
Hi @achourey,
Since we haven’t heard back, I’ll close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.
