why is my date filter is not filtering correctly?

[wpf_]

I have a date table using the calendar function setting it manually and I have a fact table.  When I select all dates, all the records show up.  But when I select a single date using dropdown, no records show.  When i use 'between' in the date slicer, for example between 3/7/2022 - 3/9/2022, it only shows records from 3/7 and 3/8.  It only shows 3/9 records when i change the slicer to 3/7/2022 - 3/10/22.  

I have created a relationship between the date slicer and the fact table.  it is a 1 to many relationship and signle direction from date table to fact table.  This is a active relationship.  

It only works if I use the fact table date column as the date slicer currently.

[v-yanjiang-msft · Accepted solution]

Hi @wpf_ ,

According to your description, I reproduce your problem, change the data type of the copied column to Date in Power Query, not only in Desktop.

Best Regards,
Community Support Team _ kalyj

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

[v-yanjiang-msft · Accepted solution]

Hi @wpf_ ,

According to your description, I reproduce your problem, change the data type of the copied column to Date in Power Query, not only in Desktop.

Best Regards,
Community Support Team _ kalyj

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

[wpf_ · Accepted solution]

@v-yanjiang-msft 

Yes i think that is the problem.....it is working now. Thank you

[speedramps]

Ha Ha Ha please show a screen shot of the data from both tables in modelling mode, rather than a visual.

when you say "changed" are you sure that it is not still a date time value.

Even the heading still same dateandtime !!!!

Try use Date.From(table[datetime]) in you query to be sure you are extracting the date .

Also be consistent and use the calandar date in the slicer and the report visual.

Dont use calendar date in the slicer and then isd(date time) in other.

The PBI slicers do work !

Accept that you have done something wrong and look hard and find it., or keep asking if you need help.

Good luck

Please click thumbs up and Accept As Solution 😀

[wpf_]

@speedramps 

I used Date.From(table[datetime]) in power query and used this in the visual, and it's doing the same thing. 

[wpf_]

Oh i forgot to mention this is a direct query.  Would this affect it?

[wpf_]

yes it is changed from date/time to date.  That is a copy of the date/time field, then i changed to date.  

[speedramps]

Check your data types are date and not date time.

If you still have problems try share the PBIX or a screen print of the both tables and the relationships but hide any private data

Please click thumbs up and Accept as solution. Thanks. 😀

[wpf_]

Here are my screenshots:

filtering all dates:

Filtering one date:

Filtering by date range but missing some dates.

Here is the relationship between the two:

Both have same data type of date (short date)

The date column in fact table was a date/time that i duplicated in power query then changed  to date type: