cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sababneh
Level: Powered On

Efficiency - Multiple Filtering in Gallery

Hey all,

 

I have asked this question before and got the answer from Sienna

https://powerusers.microsoft.com/t5/General-Discussion/Multiple-Filter-for-Gallery-using-Combobox/m-...

 

Now, I did the code, and used the If Statements. And it is working perfectly.

Originaly the main feature that I wanted is, a Gallery, which has Tasks, and there are Four Comboboxes that filter something in the Gallery, and I wanted that the 4 Comboboxes to be used together or not together. So the code that I did is:

If(PowersLabel.Text="Normal",If(IsBlank(first.Text)&&IsBlank(second.Text)&&IsBlank(third.Text)&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,Employee=NameLabel.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&Not(IsBlank(second.Text))&&Not(IsBlank(third.Text))&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,Employee=NameLabel.Text,TaskTarget=first.Text,TaskCenter=second.Text,Employee=third.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&Not(IsBlank(second.Text))&&IsBlank(third.Text)&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,Employee=NameLabel.Text,TaskTarget=first.Text,TaskCenter=second.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&IsBlank(second.Text)&&Not(IsBlank(third.Text))&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,Employee=NameLabel.Text,TaskTarget=first.Text,Employee=third.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&Not(IsBlank(second.Text))&&Not(IsBlank(third.Text))&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,Employee=NameLabel.Text,TaskTarget=first.Text,TaskCenter=second.Text,Employee=third.Text),"TaskID",Descending),If(IsBlank(first.Text)&&Not(IsBlank(second.Text))&&Not(IsBlank(third.Text))&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,Employee=NameLabel.Text,TaskCenter=second.Text,Employee=third.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&IsBlank(second.Text)&&IsBlank(third.Text)&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,Employee=NameLabel.Text,TaskTarget=first.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&IsBlank(second.Text)&&Not(IsBlank(third.Text))&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,Employee=NameLabel.Text,TaskTarget=first.Text,Employee=third.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&Not(IsBlank(second.Text))&&IsBlank(third.Text)&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,Employee=NameLabel.Text,TaskTarget=first.Text,TaskCenter=second.Text),"TaskID",Descending),If(IsBlank(first.Text)&&Not(IsBlank(second.Text))&&IsBlank(third.Text)&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,Employee=NameLabel.Text,TaskCenter=second.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(IsBlank(first.Text)&&Not(IsBlank(second.Text))&&Not(IsBlank(third.Text))&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,Employee=NameLabel.Text,TaskCenter=second.Text,Employee=third.Text),"TaskID",Descending),If(IsBlank(first.Text)&&IsBlank(second.Text)&&Not(IsBlank(third.Text))&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,Employee=NameLabel.Text,Employee=third.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&IsBlank(second.Text)&&IsBlank(third.Text)&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,Employee=NameLabel.Text,TaskTarget=first.Text),"TaskID",Descending),If(IsBlank(first.Text)&&Not(IsBlank(second.Text))&&IsBlank(third.Text)&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,Employee=NameLabel.Text,TaskCenter=second.Text),"TaskID",Descending),If(IsBlank(first.Text)&&IsBlank(second.Text)&&Not(IsBlank(third.Text))&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,Employee=NameLabel.Text,Employee=third.Text),"TaskID",Descending),If(IsBlank(first.Text)&&IsBlank(second.Text)&&IsBlank(third.Text)&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,Employee=NameLabel.Text,TaskSubject=fourth.Text),"TaskID",Descending))))))))))))))))),If(PowersLabel.Text="Super",If(IsBlank(first.Text)&&IsBlank(second.Text)&&IsBlank(third.Text)&&IsBlank(fourth.Text),SortByColumns(TasksData,"TaskID",Descending),If(Not(IsBlank(first.Text))&&Not(IsBlank(second.Text))&&Not(IsBlank(third.Text))&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,TaskTarget=first.Text,TaskCenter=second.Text,Employee=third.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&Not(IsBlank(second.Text))&&IsBlank(third.Text)&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,TaskTarget=first.Text,TaskCenter=second.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&IsBlank(second.Text)&&Not(IsBlank(third.Text))&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,TaskTarget=first.Text,Employee=third.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&Not(IsBlank(second.Text))&&Not(IsBlank(third.Text))&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,TaskTarget=first.Text,TaskCenter=second.Text,Employee=third.Text),"TaskID",Descending),If(IsBlank(first.Text)&&Not(IsBlank(second.Text))&&Not(IsBlank(third.Text))&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,TaskCenter=second.Text,Employee=third.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&IsBlank(second.Text)&&IsBlank(third.Text)&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,TaskTarget=first.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&IsBlank(second.Text)&&Not(IsBlank(third.Text))&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,TaskTarget=first.Text,Employee=third.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&Not(IsBlank(second.Text))&&IsBlank(third.Text)&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,TaskTarget=first.Text,TaskCenter=second.Text),"TaskID",Descending),If(IsBlank(first.Text)&&Not(IsBlank(second.Text))&&IsBlank(third.Text)&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,TaskCenter=second.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(IsBlank(first.Text)&&Not(IsBlank(second.Text))&&Not(IsBlank(third.Text))&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,TaskCenter=second.Text,Employee=third.Text),"TaskID",Descending),If(IsBlank(first.Text)&&IsBlank(second.Text)&&Not(IsBlank(third.Text))&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,Employee=third.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&IsBlank(second.Text)&&IsBlank(third.Text)&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,TaskTarget=first.Text),"TaskID",Descending),If(IsBlank(first.Text)&&Not(IsBlank(second.Text))&&IsBlank(third.Text)&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,TaskCenter=second.Text),"TaskID",Descending),If(IsBlank(first.Text)&&IsBlank(second.Text)&&Not(IsBlank(third.Text))&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,Employee=third.Text),"TaskID",Descending),If(IsBlank(first.Text)&&IsBlank(second.Text)&&IsBlank(third.Text)&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,TaskSubject=fourth.Text),"TaskID",Descending))))))))))))))))),If(PowersLabel.Text="Manager",If(IsBlank(first.Text)&&IsBlank(second.Text)&&IsBlank(third.Text)&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,ManagerName=NameLabel.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&Not(IsBlank(second.Text))&&Not(IsBlank(third.Text))&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,ManagerName=NameLabel.Text,TaskTarget=first.Text,TaskCenter=second.Text,Employee=third.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&Not(IsBlank(second.Text))&&IsBlank(third.Text)&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,ManagerName=NameLabel.Text,TaskTarget=first.Text,TaskCenter=second.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&IsBlank(second.Text)&&Not(IsBlank(third.Text))&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,ManagerName=NameLabel.Text,TaskTarget=first.Text,Employee=third.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&Not(IsBlank(second.Text))&&Not(IsBlank(third.Text))&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,ManagerName=NameLabel.Text,TaskTarget=first.Text,TaskCenter=second.Text,Employee=third.Text),"TaskID",Descending),If(IsBlank(first.Text)&&Not(IsBlank(second.Text))&&Not(IsBlank(third.Text))&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,ManagerName=NameLabel.Text,TaskCenter=second.Text,Employee=third.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&IsBlank(second.Text)&&IsBlank(third.Text)&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,ManagerName=NameLabel.Text,TaskTarget=first.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&IsBlank(second.Text)&&Not(IsBlank(third.Text))&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,ManagerName=NameLabel.Text,TaskTarget=first.Text,Employee=third.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&Not(IsBlank(second.Text))&&IsBlank(third.Text)&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,ManagerName=NameLabel.Text,TaskTarget=first.Text,TaskCenter=second.Text),"TaskID",Descending),If(IsBlank(first.Text)&&Not(IsBlank(second.Text))&&IsBlank(third.Text)&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,ManagerName=NameLabel.Text,TaskCenter=second.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(IsBlank(first.Text)&&Not(IsBlank(second.Text))&&Not(IsBlank(third.Text))&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,ManagerName=NameLabel.Text,TaskCenter=second.Text,Employee=third.Text),"TaskID",Descending),If(IsBlank(first.Text)&&IsBlank(second.Text)&&Not(IsBlank(third.Text))&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,ManagerName=NameLabel.Text,Employee=third.Text,TaskSubject=fourth.Text),"TaskID",Descending),If(Not(IsBlank(first.Text))&&IsBlank(second.Text)&&IsBlank(third.Text)&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,ManagerName=NameLabel.Text,TaskTarget=first.Text),"TaskID",Descending),If(IsBlank(first.Text)&&Not(IsBlank(second.Text))&&IsBlank(third.Text)&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,ManagerName=NameLabel.Text,TaskCenter=second.Text),"TaskID",Descending),If(IsBlank(first.Text)&&IsBlank(second.Text)&&Not(IsBlank(third.Text))&&IsBlank(fourth.Text),SortByColumns(Filter(TasksData,ManagerName=NameLabel.Text,Employee=third.Text),"TaskID",Descending),If(IsBlank(first.Text)&&IsBlank(second.Text)&&IsBlank(third.Text)&&Not(IsBlank(fourth.Text)),SortByColumns(Filter(TasksData,ManagerName=NameLabel.Text,TaskSubject=fourth.Text),"TaskID",Descending))))))))))))))))))))

It is a bit long I know.

 

Can I do it more effeciently?

And can I have the Comboboxes to show only the lists that are shown in the Gallery? For example, if I filter an employee (Combobox1) and there are not tasks such as "Pipeline" in the filtered gallery, can I get the task "Pipeline" not to show in the Tasks Combobox (Combobox2)

 

Thanks Smiley Happy 


Accepted Solutions
Highlighted
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Efficiency - Multiple Filtering in Gallery


It is a bit long I know.

You're correct, it's just a bit long Smiley Happy

You can combine the ifs as conditions of the filter itself. The expression below should be equivalent to the one you have (please check, it's hard to be sure for a 10000+ characters expression)

Switch(
    PowersLabel.Text,
    "Normal",
        SortByColumns(
            Filter(
                TasksData,
                Employee = NameLabel.Text,
                IsBlank(first.Text) Or TaskTarget = first.Text,
                IsBlank(second.Text) Or TaskCenter = second.Text,
                IsBlank(third.Text) Or Employee = third.Text,
                IsBlank(fourth.Text) Or TaskSubject = fourth.Text),
            "TaskID",
            Descending),
    "Super",
        SortByColumns(
            Filter(
                TasksData,
                IsBlank(first.Text) Or TaskTarget = first.Text,
                IsBlank(second.Text) Or TaskCenter = second.Text,
                IsBlank(third.Text) Or Employee = third.Text,
                IsBlank(fourth.Text) Or TaskSubject = fourth.Text),
            "TaskID",
            Descending),
    "Manager",
        SortByColumns(
            Filter(
                TasksData,
                ManagerName = NameLabel.Text,
                IsBlank(first.Text) Or TaskTarget = first.Text,
                IsBlank(second.Text) Or TaskCenter = second.Text,
                IsBlank(third.Text) Or Employee = third.Text,
                IsBlank(fourth.Text) Or TaskSubject = fourth.Text),
            "TaskID",
            Descending))

You can even make it more concise, but I don't know if it makes it easier to understand:

SortByColumns(
    Filter(
        TasksData,
        PowersLabel.Text = "Super" Or
(PowersLabel.Text = "Normal" And Employee = NameLabel.Text) Or
(PowersLabel.Text = "Manager" And Manager = NameLabel.Text), IsBlank(first.Text) Or TaskTarget = first.Text, IsBlank(second.Text) Or TaskCenter = second.Text, IsBlank(third.Text) Or Employee = third.Text, IsBlank(fourth.Text) Or TaskSubject = fourth.Text), "TaskID", Descending)

All Replies
Highlighted
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Efficiency - Multiple Filtering in Gallery


It is a bit long I know.

You're correct, it's just a bit long Smiley Happy

You can combine the ifs as conditions of the filter itself. The expression below should be equivalent to the one you have (please check, it's hard to be sure for a 10000+ characters expression)

Switch(
    PowersLabel.Text,
    "Normal",
        SortByColumns(
            Filter(
                TasksData,
                Employee = NameLabel.Text,
                IsBlank(first.Text) Or TaskTarget = first.Text,
                IsBlank(second.Text) Or TaskCenter = second.Text,
                IsBlank(third.Text) Or Employee = third.Text,
                IsBlank(fourth.Text) Or TaskSubject = fourth.Text),
            "TaskID",
            Descending),
    "Super",
        SortByColumns(
            Filter(
                TasksData,
                IsBlank(first.Text) Or TaskTarget = first.Text,
                IsBlank(second.Text) Or TaskCenter = second.Text,
                IsBlank(third.Text) Or Employee = third.Text,
                IsBlank(fourth.Text) Or TaskSubject = fourth.Text),
            "TaskID",
            Descending),
    "Manager",
        SortByColumns(
            Filter(
                TasksData,
                ManagerName = NameLabel.Text,
                IsBlank(first.Text) Or TaskTarget = first.Text,
                IsBlank(second.Text) Or TaskCenter = second.Text,
                IsBlank(third.Text) Or Employee = third.Text,
                IsBlank(fourth.Text) Or TaskSubject = fourth.Text),
            "TaskID",
            Descending))

You can even make it more concise, but I don't know if it makes it easier to understand:

SortByColumns(
    Filter(
        TasksData,
        PowersLabel.Text = "Super" Or
(PowersLabel.Text = "Normal" And Employee = NameLabel.Text) Or
(PowersLabel.Text = "Manager" And Manager = NameLabel.Text), IsBlank(first.Text) Or TaskTarget = first.Text, IsBlank(second.Text) Or TaskCenter = second.Text, IsBlank(third.Text) Or Employee = third.Text, IsBlank(fourth.Text) Or TaskSubject = fourth.Text), "TaskID", Descending)
Sababneh
Level: Powered On

Re: Efficiency - Multiple Filtering in Gallery

Beautiful code Smiley Happy thanks

Sababneh
Level: Powered On

Re: Efficiency - Multiple Filtering in Gallery

Carlos can you please explain it to me?

 

SortByColumns(
    Filter(
        TasksData,
        PowersLabel.Text = "Super" Or
            (PowersLabel.Text = "Normal" And Employee = NameLabel.Text) Or
            (PowersLabel.Text = "Manager" And Manager = NameLabel.Text),
        IsBlank(first.Text) Or TaskTarget = first.Text,
        IsBlank(second.Text) Or TaskCenter = second.Text,
        IsBlank(third.Text) Or Employee = third.Text,
        IsBlank(fourth.Text) Or TaskSubject = fourth.Text),
    "TaskID",
    Descending)
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: Efficiency - Multiple Filtering in Gallery


@Sababneh wrote:

Carlos can you please explain it to me?

 

SortByColumns(
    Filter(
        TasksData,
        PowersLabel.Text = "Super" Or
            (PowersLabel.Text = "Normal" And Employee = NameLabel.Text) Or
            (PowersLabel.Text = "Manager" And Manager = NameLabel.Text),
        IsBlank(first.Text) Or TaskTarget = first.Text,
        IsBlank(second.Text) Or TaskCenter = second.Text,
        IsBlank(third.Text) Or Employee = third.Text,
        IsBlank(fourth.Text) Or TaskSubject = fourth.Text),
    "TaskID",
    Descending)

Sure. You can pass multiple conditions to the  Filter function, and the semantics is that the record will be returned only if all of the conditions are true. In the (huge) expression that you had, there were three parts:

  • The case where PowersLabel.Text="Normal", in which case every filter operation had a 'Employee=NameLabel.Text' condition
  • The case where PowersLabel.Text="Super", in which case no filter operation had any conditions other than the ones for TaskTarget / TaskCenter / Employee / TaskSubject
  • The case where PowersLabel.Text="Manager", in which case every filter operation had a 'ManagerName=NameLabel.Text' condition

In the three parts, all other conditions are the same:

  • If first.Text is not blank, then use it to filter based on TaskTarget
  • If second.Text is not blank, then use it to filter based on TaskCenter
  • If third.Text is not blank, then use it to filter based on Employee
  • If fourth.Text is not blank, then use it to filter based on TaskSubject

The latter gives us the last four parameters to the filter expression:

IsBlank(first.Text) Or TaskTarget = first.Text,
IsBlank(second.Text) Or TaskCenter = second.Text,
IsBlank(third.Text) Or Employee = third.Text,
IsBlank(fourth.Text) Or TaskSubject = fourth.Text

For each of the (first, second, third, fourth) text inputs:

  • If it's blank, then the result of the Or operator will be true, meaning that it will not be used to filter out anything
  • If it's not blank, then it will be used to filter the corresponding column

Now for the first part, since the "inside" of the three parts are the same (let's call it C), we can use the distributive property of the conjunction - (A and C) or (B and C) == (A or B) and (C) - to combine the three parts into a single expression:

PowersLabel.Text = "Super" Or
    (PowersLabel.Text = "Normal" And Employee = NameLabel.Text) Or
    (PowersLabel.Text = "Manager" And Manager = NameLabel.Text),

If the PowersLabel is "Super", then nothing else needs to be filtered out (this condition is true). If its value is "Normal", then we'll filter based on the Employee column, and finally if the value is "Manager", we'll filter based on the Manager column.

 

And now that we have a single Filter expression that encompasses all conditions, we can use it inside a SortByColumns expression to get the result you want.

 

Hope this helps!