cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wpf_
Post Prodigy
Post Prodigy

why is my date filter is not filtering correctly?

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.

2 ACCEPTED SOLUTIONS
v-kalyj-msft
Community Support
Community Support

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.

vkalyjmsft_0-1652177912454.png

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.

View solution in original post

@v-kalyj-msft 

 

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

View solution in original post

8 REPLIES 8
v-kalyj-msft
Community Support
Community Support

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.

vkalyjmsft_0-1652177912454.png

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-kalyj-msft 

 

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

speedramps
Super User
Super User

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 😀

 

 

 

 

 

 

@speedramps 

 

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

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

wpf__0-1651692904227.png

wpf__1-1651692959902.png

wpf__2-1651693051777.png

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

 

speedramps
Super User
Super User

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. 😀

Here are my screenshots:

 

filtering all dates:

wpf__0-1651691380532.png

 

Filtering one date:

wpf__1-1651691406056.png

Filtering by date range but missing some dates.

wpf__2-1651691462587.png

 

Here is the relationship between the two:

wpf__3-1651691574798.png

 

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:

wpf__4-1651691600467.png

 

 

 

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.