How to join two filters

(0) Share

Report

Posted on by jbrines

1,151

Hi Guys,

I have two filters, one has a sort in it. how do I make one of of the following two?

SortByColumns(
 Filter(
 'Activity Tracker', 
 StartsWith('Created By'.Email, glbUser.Email) ), 
 "ID",
 If(SortDescending1, Descending, Ascending)
)

and

Filter(
 'Activity Tracker',
 'Date of Activity' >= DateAdd(Today(), 1 - Weekday(Today(), StartOfWeek.Sunday)),
 'Date of Activity' < DateAdd(Today(), 7 + 1 - Weekday(Today(), StartOfWeek.Sunday)))

Categories:

Building Power Apps

All responses (24)

Answers (3)

Sort by

Verified answer

Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @jbrines

A quicky solution:

With({wactivity: SortByColumns( Filter( 'Activity Tracker', StartsWith('Created By'.Email, glbUser.Email) ), "ID", If(SortDescending1, Descending, Ascending) )}, Filter( wactivity, 'Date of Activity' >= DateAdd(Today(), 1 - Weekday(Today(), StartOfWeek.Sunday)), 'Date of Activity' < DateAdd(Today(), 7 + 1 - Weekday(Today(), StartOfWeek.Sunday))) )
jbrines 1,151 on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp Many thanks.
jbrines 1,151 on at

Like
a
(0)

Report
Copy link

Link copied!
@Drrickryp

Can you help with this one, as it has a sum before the second filter?

Filter('Activity Tracker', StartsWith('Created By'.Email, glbUser.Email))

Join to this

Sum(Filter('Activity Tracker','Date of Activity' >= Date(Year(Today()), Month(Today()), 1) && 'Date of Activity' <= Date(Year(Today()), Month(Today()) + 1, 0)),Distance)
jbrines 1,151 on at

Like
a
(0)

Report
Copy link

Link copied!
@Drrickryp

I have managed to put this code together but it is counting for all users in the list instead of the person who is in the list.

With({wactivity: SortByColumns( Filter( 'Activity Tracker', StartsWith('Created By'.Email, glbUser.Email) ), "ID", If(SortDescending1, Descending, Ascending) )}, Sum(Filter('Activity Tracker','Date of Activity' >= Date(Year(Today()), Month(Today()), 1) && 'Date of Activity' <= Date(Year(Today()), Month(Today()) + 1, 0)),Distance) )

The code
StartsWith('Created By'.Email, glbUser.Email)

Makes the logged on user but it is adding up all the users.

J
jbrines 1,151 on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp Any ideas on how I resolve the issue?
Verified answer

Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
Yep. You have substituted the original datasource, 'Activity Tracker' back into your formula instead of "wactivity", the sorted filtered temporary variable created in the With() statement. That is why you are getting all of the members instead of just the one you want.

With({wactivity: SortByColumns( Filter( 'Activity Tracker', StartsWith('Created By'.Email, glbUser.Email) ), "ID", If(SortDescending1, Descending, Ascending) )}, Sum(Filter(wactivity,'Date of Activity' >= Date(Year(Today()), Month(Today()), 1) && 'Date of Activity' <= Date(Year(Today()), Month(Today()) + 1, 0)),Distance) )

Sum(Filter('Activity Tracker', 'Date of Activity' >= Date(Year(Today()), Month(Today()), 1) && 'Date of Activity' <= Date(Year(Today()), Month(Today()) + 1, 0)),Distance)
jbrines 1,151 on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp

See error below.
jbrines 1,151 on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp Fixed it too many spaces in 'Date of Activity'

Cheers

J
Drrickryp Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
jbrines 1,151 on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp How would I add the weekly formula in to the one you gave for the monthly formula?

Cheers
J