Filtering Gallery on SP column value "and" if last item

[juan_fungi]

Hello all! 

I'm working with a gallery control and a few variables with the intent of allowing users to filter benchmark items based on predefined themes. The problem I am running into is I only want benchmark items to return if the record is the most recent item submitted by a specific team. 

My Data is in SharePoint. Columns of interest are

 - Created (created date)

 - Huddle Description (team huddle name)

 - Customer Satisfaction (DropDown, ["Needs help"; "Knows what to do"; "Benchmark-able"])

 - Collaboration (DropDown, ["Needs help"; "Knows what to do"; "Benchmark-able"])

 - Team Engagement (DropDown, ["Needs help"; "Knows what to do"; "Benchmark-able"])

 - Problem Solving (DropDown, ["Needs help"; "Knows what to do"; "Benchmark-able"])

So back the the gallery, when I user selects desired theme button, items returned are benchmark-able and newest date from huddle description (team huddle name). 

Here is what I currently have. 

Gallery Items:
If(
BMHuddles = "Collaboration", Filter('Huddle Observation App', ThisIsBenchmarkableText_1.Text = Collaboration),
BMHuddles = "Team Engagement", Filter('Huddle Observation App', ThisIsBenchmarkableText_1.Text = TeamEngagement),
BMHuddles = "Customer Satisfaction", Filter('Huddle Observation App', ThisIsBenchmarkableText_1.Text = CustomerSatisfaction),
BMHuddles = "Problem Solving", Filter('Huddle Observation App', ThisIsBenchmarkableText_1.Text = ProblemSolving)
)

I would really appreciate any advice on this. 

Thanks, 

[WarrenBelz]

Hi @juan_fungi ,

I may be over-simplifying this (I am not sure where the Text reference comes from), but

First(
   Filter(   
      'Huddle Observation App'
      BMHuddles=ThisIsBenchmarkableText_1.Text
   )
)

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.

[juan_fungi]

@WarrenBelz 

Thank you for the super speedy response. It honestly may be that simple, and I'm just making this more complicated than necessary. Let me provide additional context. My organization has approximately 100 Business Units, 700 active team huddles. The main focus of my app is for huddle leaders to evaluate their teams huddles weekly/monthly against the following 4 focus areas; Customer Focus, Collaboration, Team Engagement and Problem Solving. Each of these focus areas are columns in a SP list and may be rated as "Needs Help", Knows what to do" or "Benchmark-able". I am attempting to add new functionality allowing leaders to search for huddles that have been identified as Benchmarks within the specific focus area. So if I was a huddle leader and I wanted to see a team that has benchmark team engagement as example, I could open the app, navigate to Benchmarks screen, select the team engagement button and the gallery filters on the team engagement column, returning huddles with benchmark rating. The issue I'm running up against is the following. 
If a team huddle is has been rated benchmark-able multiple times, I only want to see the last record. If the team huddle rated benchmark-able on 6/29/2020, then knows what to do on 7/6/2020, I don't want the 6/29/2020 returning as the tea huddle is no longer at benchmark level. 

The text reference is something I had in the app when troubleshooting and is not necessary. I just updated to 

If(
BMHuddles = "Collaboration", Filter('Huddle Observation App', "This is benchmark-able" = Collaboration),
BMHuddles = "Team Engagement", Filter('Huddle Observation App', "This is benchmark-able" = TeamEngagement),
BMHuddles = "Customer Satisfaction", Filter('Huddle Observation App', "This is benchmark-able" = CustomerSatisfaction),
BMHuddles = "Problem Solving", Filter('Huddle Observation App', "This is benchmark-able" = ProblemSolving)
)

While I was eating dinner I had an idea that on app start. I could collect the last record entered for each team huddle with the 4 focus areas (still separate columns), then in the app, filter the collection for benchmarks within the focus areas??? Is this overkill?  

Thank you again for trying to guide me through this! 

[WarrenBelz]

Hi @juan_fungi ,

Using your syntax (which I cannot test), the below may do what you want

Last(
   Filter(
      'Huddle Observation App', 
      Switch(
         BMHuddles,
         "Collaboration", "This is benchmark-able" = Collaberation,
         "Team Engagement", "This is benchmark-able" = TeamEngagement,
         "Customer Satisfaction", "This is benchmark-able" = CustomerSatisfaction, 
         "Problem Solving", "This is benchmark-able" = ProblemSolving
      )
   )
)

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.

[juan_fungi]

Thank you for your patience with me as I sort myself out. 

The syntax you provided seemed to work although it was creating delegation errors. 
I experimented with several variations of your syntax and landed on the following:

Switch(BMHuddles,
"Collaboration", Filter('Huddle Observation App', "This is benchmark-able" = Collaboration),
"Team Engagement", Filter('Huddle Observation App', "This is benchmark-able" = TeamEngagement),
"Customer Satisfaction", Filter('Huddle Observation App', "This is benchmark-able" = CustomerSatisfaction),
"Problem Solving", Filter('Huddle Observation App', "This is benchmark-able" = ProblemSolving)
)

In my image below. my variable BMHuddles = "Team Engagement so the gallery is displaying all records where Team engagement was rated as Benchmark-able. Sweet! Really happy this is working. I added another image that I believe highlights the issue I am trying to resolve. The gallery is displaying 2 benchmark records for the Training & A3 Coaching Huddle. The first 3/17/2020 and the second 5/27/2020. How can I limit to only show 1 record per Huddle, if the last record was rated "Benchmark-able". 

The huddle name is coming from same SP list column name =  Huddle_x0020_Name 

Again, thank you for your patience... I've been working on longer than expected and am starting to get red eyes... Need to disconnect. 

[WarrenBelz · Accepted solution]

Hi @juan_fungi ,

That was why I had the "Last" filter, so probably going the "long" way and using your structure

Switch(
   BMHuddles,
   "Collaboration", 
   Last(   
      Filter(
         'Huddle Observation App', 
         "This is benchmark-able" = Collaboration
      )
   (,
   "Team Engagement", 
   Last(
      Filter(
         'Huddle Observation App', 
         "This is benchmark-able" = TeamEngagement
      )
   ),
   "Customer Satisfaction", 
   Last(
      Filter(
         'Huddle Observation App', 
         "This is benchmark-able" = CustomerSatisfaction
      )
   ),
   "Problem Solving", 
   Last(
      Filter(
         'Huddle Observation App', 
         "This is benchmark-able" = ProblemSolving
      )
   )
)

Is this what you are trying to achieve?

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.

[juan_fungi]

Thank you again for you time thinking through y question. I was able to achieve my desired outcome, it may not be the cleanest solution or most responsive but seems to be working now. I updated the buttons above the gallery to include the below code, the for the gallery, set the items property to the Data collection. The code below reflects the onselect property of the Collaboration button. 

Set(BMHuddles, "Collaboration");

ClearCollect(Data, 
   ForAll(
        GroupBy(
                AddColumns(
                    Filter('Huddle Observation App', ThisIsBenchmarkableText.Text = Collaboration),
                    "new huddle name",
                    Huddle_x0020_Name
                ),
            "new huddle name",
            "Huddle Name"
        ),
            Last('Huddle Name')
    ) 
)

This is the outcome.