Using multiple Filters

[Anonymous]

Hi Guys,

I have a gallery which`s based on 3 Drop Down Menu`s. So the first Filter shows the current period. and the second one Status, Groep, and the last one is to Filter the history for the Period. 

As you maybe noticed Period is a column in my table. So what I want to achieve is that when a user open the app that he by default see the items of currently period. But when I use the last drop down to see the history then this will not work. The reason is that the first Filter for current period block this. Now I am struggling with this and I really don`t know how to achieve this. 

Spoiler

Filter('[dbo].[Kopie van operationeel]', Year(Period) = Year(Now()) && Month(Period) = Month(DateAdd(Today(), 0, Months)) &&
Status = Dropdown2_3.SelectedText.Result &&
Groep = Dropdown2_2.SelectedText.Value &&
Text(Dropdown2_5.Selected.Period,"[$-en-US]mm/yyyy") = Text(Period,"[$-en-US]mm/yyy"))

 Thanks in advance!

[Mithrandil]

Try splitting up the filter to see what part works, and what part doesn't.
Then build it part by part to see where it breaks.
So start with Filter('[dbo].[Kopie van operationeel]', Year(Period) = Year(Now())) - if that works, add the next part 🙂

[Mithrandil]

Text(Dropdown2_5.Selected.Period,"[$-en-US]mm/yyyy") = Text(Period,"[$-en-US]mm/yyy"))

You might want to reverse this one - looks like you are trying to filter the control, not a column in the source.

[Anonymous]

Hi @Mithrandil ,

I tried that, the last Drop Down Menu which is about the Period doesn`t work because like I said the first Filter current period block this.

But I want to see my items by default on the current period/date and I also want to Filter the history.

[Mithrandil]

Do you want to filter by current period/date And History, or current period/date Or History?

In the first case you could try nesting the filters:

Filter(Filter( Source, Dropdown 1 And Dropdown2), Dropdown 3)

This will first filter the datasource with the part, and then filter the result through the selection of dropdown 3

[Anonymous]

Hi @Mithrandil ,

Thank you for quick response! But unfortunately it still does not work. I don`t see any data in my gallery. So as you can see in my gallery, I want to see all the items from this current period. Then I want also to be able to select the history of the data. If I remove the last Drop Down Menu then everything works correctly, when I remove the first filter then everything works correctly, but I need to use this 2 filters in the same time.

[v-xida-msft]

Hi @Anonymous ,

Could you please share more details about the formula you typed within the Items property of the last Dropdown box?

Is the "Period" column a Date time type column in your SQL Table?

Please consider take a try with the following workaround:

Set the Items property of the Gallery to following:

Filter(
       '[dbo].[Kopie van operationeel]', 
        Year(Period) = Year(Now()) && Month(Period) = Month(Now()),
        If(
           !IsBlank(Dropdown2_3.SelectedText.Result),
           Status = Dropdown2_3.SelectedText.Result,
            true
        ),
        If(
           !IsBlank(Dropdown2_2.SelectedText.Value),
           Groep = Dropdown2_2.SelectedText.Value,
           true
        ),
        If(
           !IsBlank(Dropdown2_5.Selected.Period),
           Text(Dropdown2_5.Selected.Period, "[$-en-US]mm/dd/yyyy HH:mm") = Text(Period,"[$-en-US]mm/dd/yyyy HH:mm"),  // Modify formula here
           true
        )
)

or

Filter(
       '[dbo].[Kopie van operationeel]', 
        Year(Period) = Year(Now()) && Month(Period) = Month(Now()),
        If(
           !IsBlank(Dropdown2_3.SelectedText.Result),
           Status = Dropdown2_3.SelectedText.Result,
            true
        ),
        If(
           !IsBlank(Dropdown2_2.SelectedText.Value),
           Groep = Dropdown2_2.SelectedText.Value,
           true
        ),
        If(
           !IsBlank(Dropdown2_5.Selected.Period),
           Text(Dropdown2_5.Selected.Period, "[$-en-US]yyyymmddHHmm") = Text(Period,"[$-en-US]yyyymmddHHmm"),  // Modify formula here
           true
        )
)

On your side, you could consider use ComboBox control to list your dropdown options instead of Dropdown control, the default value within the ComboBox would be Blank in default, then you could work well with above formula.

Note: Based on the needs that you mentioned, I think you could consider use ComboBox to list the avaiable options instead of Dropdown control. Then you could filter your Gallery Items based on the any combinations of your three filter Dropdowns.

Best regards,

[Anonymous]

Hi @v-xida-msft ,

I tried the formule you gave me. Unfortunately I don`t see any data in my ComboBox Period which is a datetime value in my SQL table. The other ComboBox`s works perfectly. Do you know how to solve this?

Thanks in advance!

[v-xida-msft]

Hi @Anonymous ，

Set the Items property of the Period ComboBox to following:

Distinct('[dbo].[Kopie van operationeel]', Period)

Then set the Result property as Display value within the Period ComboBox.

Best regards, 

[Anonymous]

Hi @v-xida-msft ,

This is the formula of my Gallery see below. I got unfortunately an error at the last If statement for ComboBox Period.

The formula for ComboBox works now correctly. 

Spoiler

Distinct('[dbo].[Kopie van operationeel]', Text(Period,"[$-en-US]dd/mm/yyyy"))

Spoiler

Filter(
'[dbo].[Kopie van operationeel]',
Year(Period) = Year(Now()) && Month(Period) = Month(Now()),
If(
!IsBlank('ComboBox Status'.Selected.Result),
Status = 'ComboBox Status'.Selected.Result,
true
),
If(
!IsBlank('ComboBox Groep'.Selected.Groep),
Groep = 'ComboBox Groep'.Selected.Groep,
true
),
If(
!IsBlank('ComboBox Period'.Selected.Period),
Text('ComboBox Period'.Selected.Period, "[$-en-US]dd/mm/yyyy") = Text(Period,"[$-en-US]dd/mm/yyyy"), // Modify formula here
true
)
)