Complex Filter - GroupBy, First or Distinct?

[MikeTwister]

Hi All.

Imagine the following data structure: 

ProjectName	User	Approved/Denied	Approved/Denied Date
Winter	Mike	Removed	07/11/2018
Summer	Mike	Approved	06/11/2018
Spring	Mike	Approved	05/11/2018
Winter	Mike	Approved	05/11/2018
October	Mike	Denied	04/11/2018

Mike has been busy and has been joing projects all over the place. However, Mikes manager has removed Mike from a project with a Flow and triggered this top entry to appear. 

How can I filter a Gallery to show only the items Mike has been approved for, but at the same time hide ones that he has subsiquently been removed from?

So using the list above, his gallery should only show the following: 

ProjectName	User	Approved/Denied	Approved/Denied Date
Summer	Mike	Approved	06/11/2018
Spring	Mike	Approved	05/11/2018

The "denied" is hiding, because it has no relevance to him, and the "Winter" project is hiding because he has subsiquenty been removed and the latest item in the list shows this.

I can't figure this out! I've been rotating between using Filter/GroupBy/First - but I just cant get it .... Any help would be appreciated.

[Mike8]

Hello,

You can try this..

Button1.OnSelect = ClearCollect(Collection1,{Name:"Mike",Project:"Winter",Status:"Removed"},{Name:"Mike",Project:"Summer",Status:"Approved"},{Name:"Mike",Project:"String",Status:"Pending"},{Name:"Mike",Project:"Autumn",Status:"Approved"},{Name:"Mike",Project:"October",Status:"Denied"},{Name:"Mike",Project:"Winter",Status:"Approved"})

Gallery1.Items = Filter(Collection1,Status="Approved"&&!(LookUp(Filter(Collection1,!(Status="Approved")),Project=Collection1[@Project],Status)="Removed"))

[Drrickryp]

Hi @MikeTwister,

I know that you explored the GroupBy function but you may want to check out the part of the documentation where they talk about using GroupBy, filtering the result and then using the Ungroup() function to re-create the table. There is a good example of this near the bottom of the page regarding Cities, Countries and population.  It seems that this is similar to what you are trying to do.  Once you get the hang of it, you should be able to deal with more complicated tables than the one in your example.

ref: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-groupby

[timl]

I really like @Mike8 's formula! It's a great illustration of disambiguation syntax.
For info, the only thing I can add to it is that if you want to support other users, here's how you would amend the formula.

ClearCollect(Collection1,
             {Name:"Mike",Project:"Winter",Status:"Removed"},
             {Name:"Mike",Project:"Summer",Status:"Approved"},
             {Name:"Mike",Project:"String",Status:"Pending"},
             {Name:"Mike",Project:"Autumn",Status:"Approved"},
             {Name:"Mike",Project:"October",Status:"Denied"},
             {Name:"Mike",Project:"Winter",Status:"Approved"}, 
             {Name:"Sally",Project:"Winter",Status:"Approved"}
)

Filter(Collection1,
       Status="Approved" && 
       !(LookUp(Filter(Collection1,
                       !(Status="Approved")),Project=Collection1[@Project] && 
                       Name=Collection1[@Name]).Status
         ="Removed")  
)

[Mike8]

Thank you @timl. 🙂
Yes, it can be improved. I also like what you have added.
Also I didn't test for any bugs. The formula doesn't take into consideration the date. If you approve a user for a project, remove the user and add him again to the project, the formula will not diplay the correct data. It can be used as a reference though for MikeTwister to create what he needs. 

[MikeTwister]

Sounds good, however the projects can be nearly endless. New ones are created, and closed after time. The filter in question is basically on the "audit trail" list. This list will end up being 500+ entires long before it gets archived off. I'm assuimng you can't just create a collection of everything in the list?

It sounds confusing, I know - but I haven no real way of explaining it properly...

[v-xida-msft · Accepted solution]

Hi @MikeTwister,

Could you please share a bit more about your data source used to store the projects? Is it a SP list?

I assume that your data source is a SP list, I have made a test on my side, please take a try with the following workaround:

Set the Items proeprty of the Data Table control to following formula:

Filter(
     RenameColumns(Filter('20181109_case3','Approved/Denied'.Value="Approved"),"ProjectName","ProjectName1"),
     IsBlank(LookUp('20181109_case3',ProjectName=ProjectName1 && Approved_x002f_Denied.Value="Removed"))
)

Note: The Approved/Denied column is a Choice type coumn in my SP list. I assume that the Approved/Denied column is also a Choice type column in your SP list.

On your side, you should type:

Filter(
     RenameColumns(Filter('YourSPList','Approved/Denied'.Value="Approved"),"ProjectName","ProjectName1"),
     IsBlank(LookUp('YourSPList',ProjectName=ProjectName1 && Approved_x002f_Denied.Value="Removed"))
)

Above formula may cause a Delegation issue, in order to get rid of this issue, please also take a try with the following workaround:

Set the OnVisible property of the first screen of your app to following formula:

ClearCollect(RecordsCollection, 'YourSPList')

Set the Items proeprty of the Data Table control to following formula:

Filter(
     RenameColumns(Filter(RecordsCollection,'Approved/Denied'.Value="Approved"),"ProjectName","ProjectName1"),
     IsBlank(LookUp(RecordsCollection,ProjectName=ProjectName1 && Approved_x002f_Denied.Value="Removed"))
)

If your SP list have more than 2000 records, in order to get rid of the Delegation issue, you could consider take a try to convert the Approved/Denied column (Choice type column) into a Single line of text type column, then the Delegation issue would be solved.

Best regards,

Kris