Lots of filtering and conditionals

[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

[ChannelDevMike]

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!

[WarrenBelz · Accepted solution]

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

[jimi_hendrix]

@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.. 

[WarrenBelz]

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.

[jimi_hendrix]

@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. 

[WarrenBelz]

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

[ChannelDevMike]

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

[WarrenBelz]

@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.

[jimi_hendrix]

@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