web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Complex Filtering of C...
Power Apps
Answered

Complex Filtering of Collection

(1) ShareShare
ReportReport
Posted on by 1,351 Super User 2026 Season 1
Hi All.
 
I am hoping for some real world feedback on some code I have for filtering a collection and computing a SUM. Based on some dropdowns on the Screen, the SUM calculation will change. I am trying to optimize the code. 
 
This is the current code:
If(StartsWith(drpDataType.Selected.KeyName, "Option Name 1"),
    Switch(
        ThisItem.Level,
        1,
        With {
            filteredRecords: Filter(
            colMyData,
            KeyType.Option1Key = drpDataType.Selected.KeyName,
            ThisItem.Level = 1 && LevelSet1 = ThisItem.LevelKey)
        },
        If(IsEmpty(filteredRecords),0,Sum(filteredRecords,OptionName1Value)),
        2,
        With {
            filteredRecords: Filter(
            colMyData,
            KeyType.Option1Key = drpDataType.Selected.KeyName,
            ThisItem.Level = 2 && LevelSet2 = ThisItem.LevelKey)
        },
        If(IsEmpty(filteredRecords),0,Sum(filteredRecords,OptionName1Value)),
        3,
        With {
            filteredRecords: Filter(
            colMyData,
            KeyType.Option1Key = drpDataType.Selected.KeyName,
            ThisItem.Level = 3 && LevelSet3 = ThisItem.LevelKey)
        },
        If(IsEmpty(filteredRecords),0,Sum(filteredRecords,OptionName1Value))
    )
,//Else 
    Switch(
        ThisItem.Level,
        1,
        With {
            filteredRecords: Filter(
            colMyData,
            KeyType.OptionOtherKey = drpDataType.Selected.KeyName,
            ThisItem.Level = 1 && LevelSet1 = ThisItem.LevelKey)
        },
        If(IsEmpty(filteredRecords),0,Sum(filteredRecords,OptionOtherValue)),
        2,
        With {
            filteredRecords: Filter(
            colMyData,
            KeyType.OptionOtherKey = drpDataType.Selected.KeyName,
            ThisItem.Level = 2 && LevelSet2 = ThisItem.LevelKey)
        },
        If(IsEmpty(filteredRecords),0,Sum(filteredRecords,OptionOtherValue)),
        3,
        With {
            filteredRecords: Filter(
            colMyData,
            KeyType.OptionOtherKey= drpDataType.Selected.KeyName,
            ThisItem.Level = 3 && LevelSet3 = ThisItem.LevelKey)
        },
        If(IsEmpty(filteredRecords),0,Sum(filteredRecords,OptionOtherValue))
    )
)
 
 
 
This is the revised code:
With(
    {
        filteredRecords: Filter(
            colMyData,
            ((StartsWith(drpDataType.Selected.KeyName, "Option Name 1") &&
            KeyType.Option1Key = drpDataType.Selected.KeyName))
            ||
            (Not(StartsWith(drpDataType.Selected.KeyName, "Option Name 1")) &&
            KeyType.OptionOtherKey = drpDataType.Selected.KeyName))
            ,
            (ThisItem.Level = 1 && LevelSet1 = ThisItem.LevelKey) ||
            (ThisItem.Level = 2 && LevelSet2 = ThisItem.LevelKey) ||
            (ThisItem.Level = 3 && LevelSet3 = ThisItem.LevelKey) 
        )
    },
        If(IsEmpty(filteredRecords),0,
        If((StartsWith(drpDataType.Selected.KeyName, "Option Name 1"),
            Sum(filteredRecords,OptionName1Value),
            Sum(filteredRecrods,OptionOtherValue)
        )
)
 
They both accomplish the same thing. The first example uses an IF and SWITCH to determine the FILTER to use. The second example pushes everything to the FILTER call to figure things out.
 
What I am looking for is some guidance or real world experience on which one is better. I already fed both of these to Copilot. It told me it thought the second example might be faster but that the first example was easier to understand.
 
If you have experience with writing complex FILTER calls, can you provide me with some feedback?
 
Thank you,
-Mark
Categories:
I have the same question (0)
  • Verified answer
    Gabriel G. Profile Picture
    831 Moderator on at
    Hi,
     
    Habitually, when I have a complex filter formula, I'm trying to set every 'Switch'-ish variables before my Filter, simply to avoid conditions inside the 'Filter'. Obviously, sometimes it is impossible to set conditional variable before coding your Filter formula, so I always keep 'With' clause as first level of my process. (not sure why, but I always coded that way. I find this way cleaner and I feel it more efficient).
     
    With
    (
        {
            wCollect: <collection>
        },
        <Process>
    )
    more than, for example:
    If(
        !IsBlank(_processMode),
        With
        (
            {
                wCollect: <collection1>
            },
            <Process1>
        ),    
        With
        (
            {
                wCollect: <collection2>
            },
            <Process2>
        ),
        ...
    )
     
    Maybe, in your case, I would put more energy into capturing your 'condition variables' (such as Level and LevelKey) to be able to use this variable only in your Filter formula, but... even with that, I'm not sure you will gain performance :).

    So, I vote for your second example, not for performance cause I think it is pratically the same, but simply for the clean side it brings. It is a more pleasant coder's eye experience. I would be curious to do benchmarks on these. :D
     
    I hope it helps!
    __________________________________________________________
     
    Please click Does this answer your question 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 a Like.
  • Verified answer
    Michael E. Gernaey Profile Picture
    53,958 Moderator on at
     
    When I look at things like this I am looking at 
     
    1. Supportability: we are not always going to be the people who are supporting this and many times the next person is new, and making it simpler to read and follow is in fact a good thing
     
    2. Functional 1: This is what it is now, but what will it, or what possibly will it need to evolve into later on? Which one is more sustainable (its a part of the functional view)
     
    3. Functional Part 2: When doing complex filters, you start running into the probability of hitting Delegation issues sooner than later. For this reason, your first option, lends itself to a more manageable, albeit longer, but more... (imho) followable aka supportable. 
     
    4. Data accuracy. I tend to find that when people write them separately, they get their logic correct, but joined together they misplace And/Or ( and ) etc
     
    As you start to build more columns, more filters etc, to me adding a massive And And and works, it does, until it doesn't. Will you ever hit that.. I dont know. if it faster (as supposedly ChatGPT or whatever said), well to me the jury is out on that. 
     
    When I can, I am good with a certain "level" of complex Conditional filter as your second version, but, when I start, I try to look at how can I compartmentalize it so that my subparts are 100% delegatable, and then and only then, if putting it altogether makes it work without issue, without concern for future proofing, then I go with the shorter version.
     
    Each case, can be situation to some degree but in the end you are still looking at
     
    1. Supportability, functional, data accuracy and future needs
     
    For me, I would use your first 1, while bigger, but I cannot validate it against your data so thats my initial instinct only
  • MarkRahn Profile Picture
    1,351 Super User 2026 Season 1 on at
    Hi @Michael E. Gernaey and @GabGadou,
     
    I appreciate both of your replies. There are a few things you touched on that I would love more insight on if you have it.
     
    Delegation - This is filtering a Collection that is in memory so there is nothing to delegate to. Or at least as I understand it.
     
    Data Accuracy - 100% agree. The data has to be accurate otherwise why bother.
     
    Supportability - I think both are supportable with proper commenting. If someone explained that the following:
                (ThisItem.Level = 1 && LevelSet1 = ThisItem.LevelKey) ||
                (ThisItem.Level = 2 && LevelSet2 = ThisItem.LevelKey) ||
                (ThisItem.Level = 3 && LevelSet3 = ThisItem.LevelKey) 
    Is the equivalent of a Switch statement on ThisItem.Level to determine which field (LevelSet1, LevelSet2, or LevelSet3) to filter on, I'm not sure you could unsee it if you wanted to.
     
    It reduced the duplication of several lines of code to 3 but as you pointed out at what cost or future cost.
     
    Technical Debt is the term I think Mark Smith uses.
     
    I am working on placing the code in App - Formulas to be better able to compare the two approaches with a switch while working in the App.
     
    In a day or so I will mark both of your replies as Answers. I want to give it a little more time to see if anyone else has some insight.
     
    I truly appreciate your feedback.
     
    -Mark
     
  • Michael E. Gernaey Profile Picture
    53,958 Moderator on at
     
    You question(s) to me were about feedback on general complex filtering not this specific example, so Delegation is always a part of that discussion, not only that, but I don't know how your collection was created and the number of times, I have had to debug someone's "filtering on a collection" just to find they hit delegation way back at the collection.. if I had a nickel.
     
    Technical Debt and Supportability are not the same thing to me.
     
    Something can have very little technical debt in complexity but is still not understandable by people who will support it next. I used to write gaming engines, 3 lines of code could confuse most people.
     
    Walking up to someone's code and understanding it immediately is supportability, not technical debt. 
     
    Having to re-write something because someone wrote it a ...well let's say a certain way, which now doesn't make sense (for any reason), is technical debt. Again, this is from writing my first program at 7, I just don't see them as the same thing.
     
    At the end, I do things for another reason too, which I didn't add in, and that is Education.
     
    Many many times, I write things in a way so that people can read it, that's great, but also because otherwise they never learn what a With() statement is, they don't learn the difference between hierarchical filtering and complex filtering. For me I don't feel one is necessarily better than another, but if I was speaking about the teams I have managed as Dev/Test/Architecture management, then sometimes my perspective was based on the team itself, it didn't always help if 1 person was.. or felt they were above the others and tried to write it that way, but then it confused everyone else.
     
    I like your question, I think its something to think about, but in the end, its yours and :-) its more, what do the people who will support it when you are OOF, or transferred or moved to a new role or job.
     
    I want it to be easy for them, not have 10 lines less code.
     
  • MarkRahn Profile Picture
    1,351 Super User 2026 Season 1 on at
     
    Thank you for replying back. I appreciate your feedback and your contributions to the community (#1 on the Leader Board for March 2025 and #4 on the Overall Leaderboard) Power Platform Power Fx is not that far removed from BASIC on a TRS-80, a TI-99, or a Commodore 64 - just a guess on what you were using at 7 in the 80s.
     
    When I mentioned "Technical Debt", I was trying to convey that "Technical Debt" refers to choices made during development—like not following standard practices—that can create challenges for maintainability and scalability down the line. This, in turn, impacts "Supportability", as it increases the effort required to maintain or update the code, especially after the original developer is OOF or no longer around.
     
    Sorry my earlier post was unclear and too brief.
     
    The end goal is to write clear and concise solutions and code which execute as efficiently as possible while being maintainable by the next person 😃 
    As you've mentioned, you've done this many times, and I’m sure your experience as an MS Press author further sharpened your skills. I admire the effort it takes to write a book to teach others programming.
     
    For anyone else who has come across this post,
    • I did try the technique of coding the "IF" and "SWITCH" into the FILTER function for the Items property of a Gallery that was retrieving data from a Table in DataVerse - not a Collection. It resulted in slower performance.
    • For an in-memory Collection, I did not see much difference in performance - but based on some valid points that Michael made - the clearer code for your team is the best approach.
     
    Again thank you for replying to my post.
     
    -Mark
     
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Users!

Kudos to our 2025 Community Spotlight Honorees

Congratulations to our 2025 community superstars!

Congratulations to the April Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
Vish WR Profile Picture

Vish WR 1,070

#2
Valantis Profile Picture

Valantis 639

#3
11manish Profile Picture

11manish 608

Last 30 days Overall leaderboard