Relationship between Calendar Dimension and Fact Table - Slicer returns dates where no data in Fact

[Anonymous]

Hi,

I have a Calendar Dimension that links to my Fact table on a key.

My Calendar Dimension feeds multiple tables, and also goes way in to the future as it's a static table in the data warehouse.

I have set the relationship between and the crossfilter to "Both", however, my slicer based on values from the Calendar Dimension returns all values and not just those in the fact table.

As you can see, the slicer has values back to 2010/11 on FinancialYear, but my Fact table only has data from April-2016 onwards. It also shows future dates way in advance as the Calendar Dimension goes far in to the future.

Any advice on how to work-around this as it could be confusing for end-users.

Thanks!

[tex628 · Accepted solution]

Place a report level filter on DischargeDateKey in your facts table saying <> BLANK. I dont know if this works, but it might! 🙂

[Anonymous]

Works great, and much easier than the various workarounds that I had been thinking up! Thanks.