Date Dimension Filter Issue

[benlynch225]

Having an issue with a Date filter on a page I'm guessing someone has run into.

I have a Date dimension table with dates from 1/1/2015 - 12/31/2030

I also have a fact table with dates from 7/1/2020 - 10/2/2020 (it will continue to add data for each day going forward.

I have a typical one-to-many relationship set up correctly between the Date dimension and fact table on Date

Here's the problem - when I add a Between slicer using the Date column from the dimension table, all of the dates appear correctly based on the dates in the fact table, but the slicer is not actually filtering the data. A quick measure counting the days shows this:

I have time series charts as well, and they display dates from 2015 to 2030, showing that the filter is not working.

If i change the selection from 7/1/2020 to 7/2/2020 or 10/2/2020 to 10/1/2020, everything filters properly:

Anyone know why this is and how to overcome it?

Thanks for the help!

[HotChilli]

Just a guess - the suggestion is that the relationship is not working properly - different data types might cause this or time in one field doesn't match up with time in the other (how was the date table created?)

You can experiment by putting the date dimension date in a table and adding a measure (Count or Sum) from the fact table.  If some of the values make sense and some don't , start investigating that.

[benlynch225]

Thanks for the reply and apologies on the delay...

I did create a test as you suggested and everything worked properly. When I added the date dimension to a table, it showed all dates from the date dimension table. I then added a measure from the fact table and the date range on the table was filtered appropriately based on the dates in the fact table.

One thing that may be causing the issue is that i have multiple fact tables, all with the same relationship to the date dimension. When I add a measure from a different fact table to the test table i mentioned above, the table still works properly, showing the correct measures for the right dates based on the given fact table. 

So maybe my question/issue is... If I have this date relationship for a couple fact tables and I add the Date column from the Date dimension onto a page, it shows all possible dates from the date dimension. I want it to show the date range of the data from the fact table(s) I am using in the visuals. Sounds like I may need to create some type of calculated column on the Date dimension table that looks at the minimum date of all fact tables and the maximum date of all fact tables and filters the Date dimension table that way?

Appreciate the help!

[HotChilli]

"add the Date column from the Date dimension onto a page, it shows all possible dates from the date dimension. I want it to show the date range of the data from the fact table(s) I am using in the visuals"

Unless your data model is weird, that's what powerbi does. A measure that's shown in the visual should be shown if it returns a value for the date range.  If there is no value, powerbi doesn't show the date.  There's a 'show items with no data' option if that is required.