Date picker not filtering tables

[arane]

The date picker is created on page using
calendar = CALENDAR(Date (2000,1,1), DATE(2040,12,31))
The slicer setting for date picker kept as Between

The 2 queries are added to create grid report and joined date from calendar table with these 2 queries
in order to filter date. the relation is active and one to many.

Issues:

The filter is working for one report and not for another.
Also the value getting shown in calendar on first go should be passed to reports which is not happening.

Also, want to know how ro debug the table on the page created using power Bi Desktop.

[arane]

Hi Philip

Thanks for the reply.

I am not sure even the filter is working or not.

for range of Year 2000-2038 it showed 2 records

kindly refer below screenshot

when erased filter and it set up with default values i.e. for year 2000-2040 it showed multiple records for 2024

soon i will build a report based on sample excel to share so that you can check at your end.

Regards,

Ashwini

[PhilipTreacy]

Hi @arane 

You said 'for range of Year 2000-2038 it showed 2 records' - is that not what you expect?

You said 'it showed multiple records for 2024' again is this not hat you expect?  If there are multiple records for 2024 then they shoudl al be shown if you include 2024 in your filter.

Please provide data and explain why you think the results as you've shown so far are wrong, and what you expect as a result.

regards

Phil

[arane]

Hi Philip

I meant if there is data for year 2024 then that should be picked up when date picker has 2000-2038 set.

I have created this sample data and its not working here as well

Country	Date
England	7/12/2024 19:00
France	7/12/2023 19:00
Italy	7/12/2022 19:00
England	7/12/2021 19:00
France	7/12/2020 19:00
Italy	7/12/2019 19:00

Can you check at your end?

Regards,

Ashwini

[v-denglli-msft]

Thanks for @PhilipTreacy 's reply.
Hi @arane ,

The time in each row of the calendar table is 12:00:00AM, while the time in each row of the date column in your sample data is 7:00:00PM.

This results in the date column in the calendar table unable to filter the example data.

You can create a date table with the following DAX, where each row of the date column has a time of 7:00:00PM, so that the date column of the date table can filter the example data.

Calendar = 
VAR _calendar = CALENDAR(DATE(2000,1,1),DATE(2040,12,31))
RETURN
SELECTCOLUMNS(_calendar,"Date",[Date]+TIME(19,0,0))

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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

[arane]

@v-denglli-msftThanks for your comment and suggestion.

I modified the excel sheet data and changed time to 07:00:00 PM and same in calendar query.

and it worked.

But with actual data the time could vary for each records. And this calendar picker is common for multiple queries added on page. It should work with all.

In that case i also tried to add a column where its only date and even calendar query with only date.

Why that didn't work? any idea?

Regards,

Ashwini

[v-denglli-msft · Accepted solution]

Hi @arane ,

You can combine the date columns from all the tables into one column and then use this column to filter those tables.

In my test, there are two tables and they both have a date column.

The date column is not quite the same for the time portion of each row.

Select the date column of a table, and then select Append Query as New.

Append the two tables into a new table and then delete the columns other than the date column. And de-duplicate the date column.

This allows you to filter the original table by the date column in that new table.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

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

[arane]

Hi @v-denglli-msft 

Currently, there are 6 views which are getting used on the page that is getting designed to fetch records. Can we append all 6 queries?

The other option i found is, since i have Direct query to database tables and not import; i have added another field in views which contains only date (here changing datatype in powerbi visual dint work since its Direct query). and then filtered the field with Calendar query. and it worked.

The option you provided seems useful in case if it works on 2 queries to append. I will use it for another page which will have less numbers of queries.

The information you provided and efforts you put is precious and mind blowing. Thank you so much!

Thanks and Regards,

Ashwini

[PhilipTreacy]

@arane 

If everything is as you describe then it should work so there must be something else.

But I'd need your PBIX file to see what that is - can you provide it?

Regards

Phil