Skip to main content

Notifications

Announcements

🌸 Community Spring Festival 2025 Challenge & Updates🌸
Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps - Power Apps Experimental Features / filter function can't ...
Power Apps - Power Apps Experimental Features
Suggested answer

filter function can't filter all the items which I want the code to pick up from the collection

(1) ShareShare
ReportReport
Posted on by Ricky24 166
Is there an expert who can help check the code?  I have a filter code that picks up some data from one collection, but the filter doesn't capture all the data I want.
Filter(
    AddColumns(MSLIST,
        VQI, If(MSID = 1, 
                Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "VQI" && MSS = 1), SCORE.Value), 
                If(MSID = 2, 
                    Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "VQI" && MSS>=1&&MSS <= 2 && MSC >2), SCORE.Value),
                    If(MSID = 3,
                        Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "VQI" && MSS <= 3 && MSC > 3), SCORE.Value),
                        If(MSID = 4,
                            Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "VQI" && MSS <= 4 && MSC > 4), SCORE.Value),
                            If(MSID = 5,
                                Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "VQI" && MSS <= 5 && MSC > 5), SCORE.Value),
                                If(MSID = 6,
                                    Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "VQI" && MSS <= 6 && MSC > 6), SCORE.Value),
                                    0
                                )
                            )
                        )
                    )
                )
            ),
        VQE, If(MSID = 1, 
                Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "VQE" && MSS = 1), SCORE.Value), 
                If(MSID = 2, 
                    Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "VQE" && MSS <= 2 && MSC > 2), SCORE.Value),
                    If(MSID = 3,
                        Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "VQE" && MSS <= 3 && MSC > 3), SCORE.Value),
                        If(MSID = 4,
                            Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "VQE" && MSS <= 4 && MSC > 4), SCORE.Value),
                            If(MSID = 5,
                                Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "VQE" && MSS <= 5 && MSC > 5), SCORE.Value),
                                If(MSID = 6,
                                    Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "VQE" && MSS <= 6 && MSC > 6), SCORE.Value),
                                    0
                                )
                            )
                        )
                    )
                )
            ),
        TFI, If(MSID = 1, 
                Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "TFI" && MSS = 1), SCORE.Value), 
                If(MSID = 2, 
                    Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "TFI" && MSS <= 2 && MSC >2), SCORE.Value),
                    If(MSID = 3,
                        Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "TFI" && MSS <= 3 && MSC > 3), SCORE.Value),
                        If(MSID = 4,
                            Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "TFI" && MSS <= 4 && MSC > 4), SCORE.Value),
                            If(MSID = 5,
                                Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "TFI" && MSS <= 5 && MSC > 5), SCORE.Value),
                                If(MSID = 6,
                                    Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "TFI" && MSS <= 6 && MSC > 6), SCORE.Value),
                                    0
                                )
                            )
                        )
                    )
                )
            ),
        TFE, If(MSID = 1, 
                Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "TFE" && MSS = 1), SCORE.Value), 
                If(MSID = 2, 
                    Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "TFE" && MSS <= 2 && MSC > 2), SCORE.Value),
                    If(MSID = 3,
                        Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "TFE" && MSS <= 3 && MSC > 3), SCORE.Value),
                        If(MSID = 4,
                            Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "TFE" && MSS <= 4 && MSC > 4), SCORE.Value),
                            If(MSID = 5,
                                Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "TFE" && MSS <= 5 && MSC > 5), SCORE.Value),
                                If(MSID = 6,
                                    Sum(Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "TFE" && MSS <= 6 && MSC > 6), SCORE.Value),
                                    0
                                )
                            )
                        )
                    )
                )
            )
    ),
    MSID < 7
)
  • Suggested answer
    Ricky24 Profile Picture
    Ricky24 166 on at
    filter function can't filter all the items which I want the code to pick up from the collection
    thank you two answer my question,it's my mistake,I have a code fault,I type in the I to instead 1,then filter can't all the items.
  • Verified answer
    stampcoin Profile Picture
    stampcoin 588 on at
    filter function can't filter all the items which I want the code to pick up from the collection
     
    it's hard to get the logic...
    Do you mean like this :
    MSID = 1: Only sum rows where MSS = 1.
    MSID = 2: Sum rows where MSS is greater than 1 and less than or equal to 2.
    MSID = 3: Sum rows where MSS is greater than 2 and less than or equal to 3, and so on.
     
    VQI , this is a difference than others :
    Value = "VQI" && MSS>=1&&MSS <= 2 && MSC >2), SCORE.Value),
     
    if you only keep one  column, for example VQI, would it give you the correct value ?
     
  • Verified answer
    Jayendran_S Profile Picture
    Jayendran_S 7 on at
    filter function can't filter all the items which I want the code to pick up from the collection
    Hi @Ricky24, Could you please provide more details on what part of the data is not picked up. 
    Also, to make the code more readable you can use the below code.
     
    /* Creating variables for each call category to reduce repetition */
    With(
        {
            /* Define base collections for each category */
            VQIItems: Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "VQI"),
            VQEItems: Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "VQE"),
            TFIItems: Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "TFI"),
            TFEItems: Filter(PMMILESTONESCORE, 'CALL CATEGORY'.Value = "TFE")
        },
        /* Now filter the main list and add columns */
     
        ClearCollect(finalOP, /*Added a collection to store the output for debugging*/
        Filter(
            AddColumns(MSLIST,
                /* VQI Column */
                VQI, Switch(MSID,
                    1, Sum(Filter(VQIItems, MSS = 1), SCORE.Value),
                    2, Sum(Filter(VQIItems, MSS <= 2), SCORE.Value),
                    3, Sum(Filter(VQIItems, MSS <= 3), SCORE.Value),
                    4, Sum(Filter(VQIItems, MSS <= 4), SCORE.Value),
                    5, Sum(Filter(VQIItems, MSS <= 5), SCORE.Value),
                    6, Sum(Filter(VQIItems, MSS <= 6), SCORE.Value),
                    0
                ),
               
                /* VQE Column */
                VQE, Switch(MSID,
                    1, Sum(Filter(VQEItems, MSS = 1), SCORE.Value),
                    2, Sum(Filter(VQEItems, MSS <= 2), SCORE.Value),
                    3, Sum(Filter(VQEItems, MSS <= 3), SCORE.Value),
                    4, Sum(Filter(VQEItems, MSS <= 4), SCORE.Value),
                    5, Sum(Filter(VQEItems, MSS <= 5), SCORE.Value),
                    6, Sum(Filter(VQEItems, MSS <= 6), SCORE.Value),
                    0
                ),
               
                /* TFI Column */
                TFI, Switch(MSID,
                    1, Sum(Filter(TFIItems, MSS = 1), SCORE.Value),
                    2, Sum(Filter(TFIItems, MSS <= 2), SCORE.Value),
                    3, Sum(Filter(TFIItems, MSS <= 3), SCORE.Value),
                    4, Sum(Filter(TFIItems, MSS <= 4), SCORE.Value),
                    5, Sum(Filter(TFIItems, MSS <= 5), SCORE.Value),
                    6, Sum(Filter(TFIItems, MSS <= 6), SCORE.Value),
                    0
                ),
               
                /* TFE Column */
                TFE, Switch(MSID,
                    1, Sum(Filter(TFEItems, MSS = 1), SCORE.Value),
                    2, Sum(Filter(TFEItems, MSS <= 2), SCORE.Value),
                    3, Sum(Filter(TFEItems, MSS <= 3), SCORE.Value),
                    4, Sum(Filter(TFEItems, MSS <= 4), SCORE.Value),
                    5, Sum(Filter(TFEItems, MSS <= 5), SCORE.Value),
                    6, Sum(Filter(TFEItems, MSS <= 6), SCORE.Value),
                    0
                )
            ),
            MSID < 7
        ))
    );

     

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

News and Announcements

🌸 Community Spring Festival 2025 Challenge & Updates🌸
Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

WIN Power Platform Community Conference 2025 tickets!

Markus Franz – Community Spotlight

We are honored to recognize Markus Franz as our April 2025 Community…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,631 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 65,976 Most Valuable Professional

Leaderboard

Featured topics

Product updates

Microsoft Power Platform Community release plans