Date filtering showing results beyond scope of date table, even with filter present

[SevsBo]

We've recently identified an interesting issue with a report.

We have a Sales table and a Date table, connected via a one-to-many relationship. The Sales table has data from 2019 to 2027, but our Date table only has dates from 2019 to 2025. This is deliberate.

When we use the Year column from the Date table, combined with Sales Amount column, we get blanks for the years that don't exists in the Date table, but do exist on the Sales table. But the blank fields also remain after we apply date-specific filters.

TL;DR: shouldn't filtering on dates remove blanks, as only the dates that exist in the date table would calculate?

This is without filters, and we can see data for years that don't exist in the Date table are showing.

But then we apply a date filter, say only show sales up to end of current year:

And nothing changes:

If we add a filter to say that Date should only be until August 2025, only the 2025 & total value is reduced, but the blanks are still showing:

Shouldn't the fact that we are filtering on pre-certain date remove data for the dates that don't exist in the Date table?

[v-hashadapu]

Hi @SevsBo , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[MFelix · Accepted solution]

Hi @SevsBo ,

This has to do with the way relationships send context to the fact table. In you case you get the related values for everythin below 2025, this means that all other field are blanks, however when you are filtering the table based on the specific date you are getting all the values for that date. Howeve if you do a filter based on a less than the fact table get's filter for every values lower than that date, the values that do not have a match are blank or 0 this means that they are lower than that value and show up on the visual.

See the example below I have done it with years but the principle is the same:

Table with no filters

Table with filters lower than 2025:

Table with filters higher than 2019

Has you can see when you refer to less than a number blank is consider lower than that number (dates are nothing more than numbers) however reversing the filter you get rid of the blanks.

I would sugest one of this approach from prefered one to less prefered:

• Filter out the values from you fact table on load that are not related (sometimes you need those values for other things)
• Increase the size of your dimension to include the other dates and then filter out on the report the dates higher than current date (Filters on all pages)
• If you don't want to change the model directly apply filter on all pages to the date table and filter out blank values

• Do the same filter on the visual and not on the pages.

This video explain the option on the slicers but the explanation is valid for all your model.

https://www.youtube.com/watch?v=23qemkDqMVY

[Ritaf1983]

Hו @SevsBo 

The issue happens because your Date dimension does not cover the full range of dates in the Fact table. Any records with dates outside of the Date table will show up as Blank, since they cannot match a valid date key, and therefore the filter does not apply to them.

Quick workaround (not recommended for the long term):
Add an additional filter condition with
AND NOT(ISBLANK(...))
This will hide blanks locally, but it is less efficient.

Proper solution:
Make sure your Date dimension includes the entire date range of your Fact table. This way, slicers and filters work consistently and you won’t get unexpected blank values.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly