cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alex93jansen
Helper II
Helper II

Not able to filter SQL data by date

I have a SQL dataset with the fields "activityDate" of data type 'Date' and 'activityHours' of data type 'Float'.

 

The following formula correctly retrieves the number of records for March 1, 2021

 

CountRows(
    Filter(
        '[dbo].[tmtrak_dev_records]',
        submittedBy = userEmail,
        activityDate = Date(2021, 3, 1)
    )
)

 

However, the following formula returns a blank value when retrieving the Sum of "activityHours" of records for March 1, 2021

 

Sum(
    Filter(
        '[dbo].[tmtrak_dev_records]',
        submittedBy = userEmail,
        activityDate = Date(2021, 3, 1)
    ),
    activityHours
)

 

When 'activityDate' is removed from the second Filter, the formula correctly retrieves the Sum of "activityHours" for all user records in my dataset.

 

I went through the documentation several times, but it's unclear to me why "activityDate" can be used in the first formula example, but not the second.

 

Any thoughts?

1 ACCEPTED SOLUTION

Accepted Solutions
mahoneypat
Dual Super User
Dual Super User

Is activityhours a numeric data type?  If not, you could wrap it in Value( ) first.

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about the Power Platform, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
mahoneypat
Dual Super User
Dual Super User

Is activityhours a numeric data type?  If not, you could wrap it in Value( ) first.

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about the Power Platform, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Thanks, this actually solved my problem! However, I do receive a Delegation warning now.

 

My database will have well over 2000 records, so this may be a new issue...

 

EDIT: activityHours has data type 'Float'

RaashiSen
Community Support
Community Support

Hello @alex93jansen ,
Thank you for confirming. If this addresses your ask, please mark mahoneypat's response as a solution.

for your delegation part, you can use variables or collections to work on this. You can get some suggestions from Forum within too.
Article to refer: Understand delegation in a canvas app - Power Apps | Microsoft Docs

alex93jansen
Helper II
Helper II

One question about delegation. The formula below throws a delegation warning due to the VALUE-function. However, will this pertain to the entire dataset size (well over 2000) or the result of FILTER (will never exceed 10)?

Sum(
    Filter(
        '[dbo].[tmtrak_dev_records]',
        submittedBy = userEmail,
        activityDate = Date(2021, 3, 1)
    ),
    VALUE(activityHours)
)

 

 

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (2,243)