cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sam_sonepar
Helper III
Helper III

Conditional Filtering - Simplifying Query

Hello,

I'd like to simply this formula on a gallery.

If(
    Dropdown7_1.Selected.Value = Blank(),
    Filter(
        MarketingFlyerList,
        FlyerPeriod = varFlyerTime,
        EditFlag = Dropdown2_2.Selected.Value || Dropdown2_2.Selected.Value = "All Items",
        PurchasingOwner = Dropdown5_2.Selected.Result || Dropdown5_2.Selected.Result = "All Items",
        SupplierName = Dropdown3_2.Selected.Result || Dropdown3_2.Selected.Result = Blank(),
        StartsWith(
            ProductCode,
            TextInput7_2.Text
        )
    ),
    If(
        Dropdown7_1.Selected.Value = "Qc",
        Filter(
            MarketingFlyerList,
            FlyerPeriod = varFlyerTime,
            EditFlag = Dropdown2_2.Selected.Value || Dropdown2_2.Selected.Value = "All Items",
            PurchasingOwner = Dropdown5_2.Selected.Result || Dropdown5_2.Selected.Result = "All Items",
            SupplierName = Dropdown3_2.Selected.Result || Dropdown3_2.Selected.Result = Blank(),
            StartsWith(
                ProductCode,
                TextInput7_2.Text
            ),
            QC = "true"
        ),
        If(
            Dropdown7_1.Selected.Value = "On",
            Filter(
                MarketingFlyerList,
                FlyerPeriod = varFlyerTime,
                EditFlag = Dropdown2_2.Selected.Value || Dropdown2_2.Selected.Value = "All Items",
                PurchasingOwner = Dropdown5_2.Selected.Result || Dropdown5_2.Selected.Result = "All Items",
                SupplierName = Dropdown3_2.Selected.Result || Dropdown3_2.Selected.Result = Blank(),
                StartsWith(
                    ProductCode,
                    TextInput7_2.Text
                ),
                ON = "true"
            ),
            If(
                Dropdown7_1.Selected.Value = "Atl",
                Filter(
                    MarketingFlyerList,
                    FlyerPeriod = varFlyerTime,
                    EditFlag = Dropdown2_2.Selected.Value || Dropdown2_2.Selected.Value = "All Items",
                    PurchasingOwner = Dropdown5_2.Selected.Result || Dropdown5_2.Selected.Result = "All Items",
                    SupplierName = Dropdown3_2.Selected.Result || Dropdown3_2.Selected.Result = Blank(),
                    StartsWith(
                        ProductCode,
                        TextInput7_2.Text
                    ),
                    ATL = "true"
                )
            )
        )
    )
)

 

Dropdown7_1 contains 3 value (Qc, On, Atl) or can be blank.

The associated SharePoint list contains 3 columns for each choice (QC, ON, ATL), which are Single Lines of Text than contain the words "true" or "false". They are not booleans, just text.

 

The query under each condition is the same. The only thing that varies is the QC/ON/ATL columns.

I'd like to convert this formula in 1 block where I would not have to repeat everything 4 times because of the 4 choices in the dropdown.

 

Ideally, it would look like the one below but slightly longer so I can specify each possible choice in the dropdown (blank, Qc, On, Atl).

Filter(
    MarketingFlyerList,
    FlyerPeriod = varFlyerTime,
    EditFlag = Dropdown2_2.Selected.Value || Dropdown2_2.Selected.Value = "All Items",
    PurchasingOwner = Dropdown5_2.Selected.Result || Dropdown5_2.Selected.Result = "All Items",
    SupplierName = Dropdown3_2.Selected.Result || Dropdown3_2.Selected.Result = Blank(),
    StartsWith(
        ProductCode,
        TextInput7_2.Text
    ),
    If(
        Dropdown7_1.Selected.Value = "Qc",
        QC = "true"
    )
)

The issue here is that it's giving me a delegation warning so I'm wondering if I'm doing something wrong.

Thanks!

8 REPLIES 8
WarrenBelz
Super User III
Super User III

Hi @sam_sonepar ,

I think you need something like this (free-typed, so watch commas and brackets etc)

Filter(
   MarketingFlyerList,
   FlyerPeriod = varFlyerTime &&
   (
      Dropdown2_2.Selected.Value = "All Items" || 
      EditFlag = Dropdown2_2.Selected.Value
   ) && 
   (   
      Dropdown5_2.Selected.Result = "All Items" || 
      PurchasingOwner = Dropdown5_2.Selected.Result
   ) && 
      Dropdown3_2.Selected.Result = Blank() || 
      SupplierName = Dropdown3_2.Selected.Result
   ) && 
   Switch(
      Dropdown7_1.Selected.Value
      Blank(),
      StartsWith(
         ProductCode,
         TextInput7_2.Text
      ),
      "Qc",
      QC = "true",
      "On",
      ON = "true"
      "Atl",
      ATL = "true"
   )
)

 

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.

@WarrenBelz 

I tried your formula, written like this.

 

Filter(
    MarketingFlyerList,
    FlyerPeriod = varFlyerTime,
    EditFlag = Dropdown2_2.Selected.Value || Dropdown2_2.Selected.Value = "All Items",
    PurchasingOwner = Dropdown5_2.Selected.Result || Dropdown5_2.Selected.Result = "All Items",
    SupplierName = Dropdown3_2.Selected.Result || Dropdown3_2.Selected.Result = Blank(),
    StartsWith(
        ProductCode,
        TextInput7_2.Text
    ),
    Switch(
        Dropdown7_1.Selected.Value,
        Blank(),
        true,
        "Qc",
        QC = "true",
        "On",
        ON = "true",
        "Atl",
        ATL = "true"
    )
)

 

 

It seems to be working fine but I'm still getting the yellow triangle and blue line saying "Delegation Warning. The filter part of the formula might not work on large dataset."

I tried to change the Data Row Limit to 1, just to see if it would limit the query but I'm still able to get all the items I have.

Would you know why I'm getting a delegation warning but no delegation "symptoms"?

Hi @sam_sonepar ,

If the top filter is a date, that may be your issue. If you start removing items until the warning goes away, you will find it, although the rest looks OK.

The top is not a date. It's actually free text like this: "NOV/DEC 2020" 

The delegation warning is on the switch itself. But...it's working despite changing the Date Row Limit from 500 to 1.

Hi @sam_sonepar ,

I think it is the true in the Switch - try this (note also your top filters would normally show the item requiring true - no filter - first)

Filter(
    MarketingFlyerList,
    FlyerPeriod = varFlyerTime,
    Dropdown2_2.Selected.Value = "All Items" || EditFlag = Dropdown2_2.Selected.Value,
    Dropdown5_2.Selected.Result = "All Items" || PurchasingOwner = Dropdown5_2.Selected.Result,
    Dropdown3_2.Selected.Result = Blank() || SupplierName = Dropdown3_2.Selected.Result,
    StartsWith(
        ProductCode,
        TextInput7_2.Text
    ),
   (
      Dropdown7_1.Selected.Value = Blank() ||
         Switch( 
           Dropdown7_1.Selected.Value,
           "Qc",
           QC = "true",
           "On",
           ON = "true",
           "Atl",
           ATL = "true"
      )
   )
)

 

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.

 

The latest formula doesn't seem to work.

(I also took the chance the adjust the previous terrible naming scheme to something easier to work with.)

 

sam_sonepar_0-1622504932868.png

 

I'm wondering if having a condition (IF or SWITCH) is just not possible inside of the Filter function.

 

@sam_sonepar ,

Interesting - I cannot test this - can you please try the structure below

Filter(
   MarketingFlyerList,
   FlyerPeriod = varFlyerTime,
   (
      Dropdown2_2.Selected.Value = "All Items" || 
      EditFlag = Dropdown2_2.Selected.Value
   ) &&
   (
      Dropdown5_2.Selected.Result = "All Items" || 
      PurchasingOwner = Dropdown5_2.Selected.Result
   ) && 
   (
      Dropdown3_2.Selected.Result = Blank() || 
      SupplierName = Dropdown3_2.Selected.Result
   ) &&
   StartsWith(
      ProductCode,
      TextInput7_2.Text
   ) && 
   (
      Dropdown7_1.Selected.Value = Blank() ||
      If(
         Dropdown7_1.Selected.Value = "Qc",
         QC = "true",
         Dropdown7_1.Selected.Value = "On",
         ON = "true",
         Dropdown7_1.Selected.Value = "Atl",
         ATL = "true"
      )
   )
)

 

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.

 

FYI, I renamed some control and changed QC/ON/ATL from string to Boolean. It's still working fine with the previous formula.

 

When I put your formula, I get this with nothing showing up.

sam_sonepar_1-1622736486628.png

 

I tried to remove the condition about the region and the delegation warning goes away and results were showing up in the gallery.

 

sam_sonepar_0-1622736431376.png

 

Should I assume that IF conditions nested in a Filter function simply don't work?

 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,186)