cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jbrit2020
Responsive Resident
Responsive Resident

Filter gallery to show only results created today

Hi all,

 

I'm kind of struggling with filtering a gallery by date.

 

I'm using a SQL database where the dates are stored in datetime2(7) format.

 

I need to look at displaying only today's dates on the gallery, and for that the only way I found is by doing:

Filter('OP.ORMSRMUpdates', Meeting= SelectedMeeting.Subject,IsToday(Dateupdate))

It works, but it throws a delegation warning.

 

How can I do this in a different way so that I get rid of the delegation warning?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
eka24
Super User III
Super User III

To eliminate the delegation warning on sql, I sugest you create another column for the Date as per documentation:

"Direct date filters do not work for SQL Server. However, you can create a calculated column that will work. For instance, ALTER TABLE myTable ADD DateAsInt AS (YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])) and then filter on the calculated number column."

 

Then use the calculated column for all filtering with sql dates

Documentation:

SQL Server - Connectors | Microsoft Docs

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

 

View solution in original post

5 REPLIES 5
eka24
Super User III
Super User III

Can you clarify what SelectedMeeting.Subject is referring to. Is it a Datepicker or a Column Name. Also DateUpdate

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

jbrit2020
Responsive Resident
Responsive Resident

SelectedMeeting.Subject is a variable selection from another gallery and DateUpdate is the date of each record in the gallery I want to filter

Ramole
Memorable Member
Memorable Member

Hi @jbrit2020 

Try this 

 

Filter( 'OP.ORMSRMUpdates', 'Date due' = Today() )    

 

No delegation warning

Thank you
If this post helps, then please consider Accept it as the solution to help the others and consider giving it a "Thumbs Up."
eka24
Super User III
Super User III

To eliminate the delegation warning on sql, I sugest you create another column for the Date as per documentation:

"Direct date filters do not work for SQL Server. However, you can create a calculated column that will work. For instance, ALTER TABLE myTable ADD DateAsInt AS (YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])) and then filter on the calculated number column."

 

Then use the calculated column for all filtering with sql dates

Documentation:

SQL Server - Connectors | Microsoft Docs

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

 

View solution in original post

jbrit2020
Responsive Resident
Responsive Resident

Thanks for the help, doesn't work unfortunately as the SQL field returns date and time and will therefore never match Today(). I will try @eka24 approach

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (3,013)