Lots of filtering and conditionals

(0) Share

Report

Posted on by jimi_hendrix

Im looking for some help around the following.

I have a gallery and of course a dataset..

I have 4 columns in that dataset, example below

Name	Office	Team	Skill
John Green	NY	A-Team	Master of the universe
Brian Red	NY	A-Team	Knight
Steve Blue	NY	A-Team	Knight
Ava Yellow	NY	A-Team	Knight
Tom Silver	AL	A-Team	Knight
Robert Purple	AL	B-Team	Master of the universe
Alex Apricot	AL	B-Team	Master of the universe
Ed Blue	AL	B-Team	Knight

Im looking to get a filtered list that would only show Tom Silver and Ed Blue..

Tom Silver cause he is the only one on the A-Team that is a Knight residing in AL..

and

Ed Blue cause he is the only Knight on the B-Team in AL..

In short, there needs to be two people on the same team, in the same office, having the same skill for them to NOT show up on the list..

Any help would be greatly appreciated

Categories:

Building Power Apps

I have the same question (0)

All responses (12)

Answers (1)

ChannelDevMike 255 on at

Like (0)

Report
Copy link

Link copied!

One way I can think of it is to exclude name and remove duplicates

Office Team Skill
NY A-Team Master of the universe
NY A-Team Knight
AL A-Team Knight
AL B-Team Master of the universe
AL B-Team Knight

Then filter on Office and Skill matching.

Probably a better way but that's one idea for ya!

Was this reply helpful? Yes No
Verified answer

WarrenBelz 154,471 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @jimi_hendrix ,

Based on what you posted, this will should get the result.

Ungroup( Filter( AddColumns( GroupBy( Filter( AddColumns( GroupBy( SPListName, "Office", "Team", "Skill", "Data" ), "Members", CountRows(Data), "Name", First(Data).Name ), Members = 1 ), "Office", "Skill", "Data2" ), "TeamMembers", CountRows(Data2) ), TeamMembers > 1 ), "Data2" )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
jimi_hendrix on at

Like (0)

Report
Copy link

Link copied!

@WarrenBelz , Thanks for your reply.. Im struggling on performing the above cause I dont know if its possible to group by a sharepoint lookup column.. Ive tried multiple different ways and havent been able to make anything work.. any suggestions..

Was this reply helpful? Yes No
WarrenBelz 154,471 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @jimi_hendrix ,

Which column/s are lookup ? They are not good ideas at the best of times (I never use them) unless you need to use the SharePoint bulk edit facility directly.

Was this reply helpful? Yes No
jimi_hendrix on at

Like (0)

Report
Copy link

Link copied!

@WarrenBelz , The team and skill columns are both lookups. One of the thoughts that I was thinking was to create a "text" add on column that can easily be referenced.. but struggling to do that as well.

Was this reply helpful? Yes No
WarrenBelz 154,471 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @jimi_hendrix ,

This should do the job based on the values you have posted

Ungroup( Filter( AddColumns( GroupBy( Filter( AddColumns( GroupBy( AddColumns( SPListName, "TeamVal", Team.Value, "SkillVal", Skill.Value ), "Office", "TeamVal", "SkillVal", "Data" ), "Members", CountRows(Data), "Name", First(Data).Name ), Members = 1 ), "Office", "SkillVal", "Data2" ), "TeamMembers", CountRows(Data2) ), TeamMembers > 1 ), "Data2" )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
ChannelDevMike 255 on at

Like (0)

Report
Copy link

Link copied!

For this, I use a calculated field. Setting this text column to Skills.Name when Skills contains data

Was this reply helpful? Yes No
WarrenBelz 154,471 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@ChannelDevMike ,

I assume you are also @jimi_hendrix ? Please explain wat you mean here - You said Skills was a Lookup column - calculated columns simply will not work here.

Was this reply helpful? Yes No
jimi_hendrix on at

Like (0)

Report
Copy link

Link copied!

@WarrenBelz , correct.. I am using a Lookup column, not a calculated column.. I have something working.. Im now running some tests to see if its doing what I expect.. Will keep you posted

Was this reply helpful? Yes No
ChannelDevMike 255 on at

Like (0)

Report
Copy link

Link copied!

I don't know if this is how calculated fields were intended to be used.. but my org uses them like this because it works.
hzy_case is a lookup field. In this case, I only needed one value from it. I can set a value in another entity this way. This column is basically just a reference to Total Task Minutes. (Which seemed to be what he was looking to do)

So my answer above was to do something similar. It would be something like hzy_skills.name (I am guessing the column in skills that provides values "Knight" and "Master ..." is the Name column in skills.) and setting his text column that he made for reference to this.

Was this reply helpful? Yes No