Using multiple Combo Boxes to filter a Data Table

[Nicky_VDB]

I'm struggling with something that should seem simple enough: I have a data table that therapists at work can see what kid they're responsible for, it includes information like: 'department', 'team', 'name',...

I want to add a few combo boxes so that the therapist can filter on these different columns.

This is the formula I'm currently using: 

If(
    Toggle_My.Value = true;
    Filter(
        '[dbo].[rAnalyseAfdelingen]';
        UserID = PN_KV
    );
    Filter(
        '[dbo].[rAnalyseAfdelingen]';
        Naam in TextInput_Name.Text Or Voornaam in TextInput_Name.Text;
        Afdeling exactin ComboBox_Afdeling.SelectedItems;
        Cluster exactin ComboBox_Cluster.SelectedItems;
        Team exactin ComboBox_Team.SelectedItems;
        Coördinator exactin ComboBox_CV.SelectedItems;
        KV exactin ComboBox_KV.SelectedItems;
        Stadium exactin ComboBox_Stadium.SelectedItems
    )
)

Which works well enough, but it adds the different filters together instead of subtracting them because of the 'Or' statements.

I have tried using And statements, but that doesn't work as it seems to think the combo boxes aren't empty when they infact are.

What I want is, if say you select a coördinator, and also a stage (stadium), you only see the children that are assigned to that coördinator in that stage, not all the kids assigned to that coördinator AND in that stage.

Any pointers would be awesome!

[mdevaney]

@Nicky_VDB 

There are many conditions in your FILTER statement but you only mentioned co-ordinator and stage so that's what i'll focus on here.  This is your code "as-is".

Filter(
        '[dbo].[rAnalyseAfdelingen]';
        Coördinator exactin ComboBox_CV.SelectedItems;
        Stadium exactin ComboBox_Stadium.SelectedItems
)

To me this says "get only the results where the Co-ordinator and the Stage match"  This is exactly your requirement!  Give it a try without all of the other filters and see if it works.

If it does not work, and the gallery behaves more like an OR condition the must be a problem with the selection in your combobox.  The SelectedItems property allows for the selection of more than one option.  Perhaps you will want to change the property AllowMutlipleSelections to

False

And then change your code to this:

Filter(
        '[dbo].[rAnalyseAfdelingen]';
        Coördinator = ComboBox_CV.Selected;
        Stadium = ComboBox_Stadium.Selected
)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Nicky_VDB]

That works sort of but not quite! 

I changed the code to your example, but when I only select one of the comboboxes, it shows nothing instead of only filtering on the one condition.

When I fill all the comboboxes it filters correctly, it makes me think there's a problem with how powerapps sees the empty values.

[v-xida-msft · Accepted solution]

Hi @Nicky_VDB ,

Could you please share a bit more about your scenario?

Do you want to subtract the filtered results based on the multiple conditions?

Please consider modify your formula as below:

If(
    Toggle_My.Value = true;
    Filter(
        '[dbo].[rAnalyseAfdelingen]';
        UserID = PN_KV
    );
    Filter(
           '[dbo].[rAnalyseAfdelingen]';
            Naam in TextInput_Name.Text || Voornaam in TextInput_Name.Text;
           If(
              !IsBlank(ComboBox_Afdeling.Selected.Value);
              Afdeling in ComboBox_Afdeling.SelectedItems;
              true
           );
           If(
              !IsBlank(ComboBox_Cluster.Selected.Value);
              Cluster in ComboBox_Cluster.SelectedItems;
              true
           );
           If(
              !IsBlank(ComboBox_Team.Selected.Value);
               Team in ComboBox_Team.SelectedItems;
               true
           );
           If(
               !IsBlank(ComboBox_CV.Selected.Value);
               Coördinator in ComboBox_CV.SelectedItems;
               true
           );
           If(
              !IsBlank(ComboBox_KV.Selected.Value);
              KV in ComboBox_KV.SelectedItems;
              true
           );
           If(
              !IsBlank(ComboBox_Stadium.Selected.Value);
              Stadium in ComboBox_Stadium.SelectedItems;
              true
           )        
    )
)

Please consider take a try with above solution, check if the issue is solved.

Best regards,

[Nicky_VDB]

I had to change the formulas a bit, but it works now! This is what I ended up using:

Thanks!

    If(
    Toggle_My.Value = true;
    Filter(
        '[dbo].[rAnalyseAfdelingen]';
        UserID = PN_KV
    );
    Filter(
        '[dbo].[rAnalyseAfdelingen]';
        If(
            !IsBlank(TextInput_Name.Text);
            Naam in TextInput_Name.Text || Voornaam in TextInput_Name.Text;
            true
        );
        If(
            !IsBlank(ComboBox_Stadium.Selected.Result);
            Stadium in ComboBox_Stadium.Selected.Result;
            true
        );
        If(
            !IsBlank(ComboBox_Afdeling.Selected.Result);
            Afdeling in ComboBox_Afdeling.Selected.Result;
            true
        );
                If(
            !IsBlank(ComboBox_Cluster.Selected.Result);
            Cluster in ComboBox_Cluster.Selected.Result;
            true
        );
        If(
            !IsBlank(ComboBox_Team.Selected.Result);
            Team in ComboBox_Team.Selected.Result;
            true
        );
        If(
            !IsBlank(ComboBox_CV.Selected.Result);
            Coördinator in ComboBox_CV.Selected.Result;
            true
        );
        If(
            !IsBlank(ComboBox_KV.Selected.Result);
            KV exactin ComboBox_KV.Selected.Result;
            true
        )
    )
)

[stefano1684]

Hi can you explain this part ? 

I need the same thing, i have different comboboxes, but i can't understand the 'togglemy.value' :s

    If(
    Toggle_My.Value = true;
    Filter(
        '[dbo].[rAnalyseAfdelingen]';
        UserID = PN_KV
    );