cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Charlie
Helper I
Helper I

How do correctly several filters with several combobox.

Hi all,

 

In my app, I add a data source (table) from excel file with 4 columns : col1, col2, col3, col4.

In each columns I can several times the same value. Fox example :

col1col2col3col4
val1.1val2.1val3.1val4.1
val1.1val2.2val3.2val4.2
val1.2val2.2val3.1val4.3
............

 

I have 4 combobox and 1 gallery and I would like create a filter suite with each combobox :

A way to do it, could be :

 

Combo1:
Distinct(myTable; col1)

Combo2:
Distinct(myTable; Filter(myTable; col1 = Combo1.Selected.Result))

Combo3:
Distinct(myTable; Filter(myTable; col1 = Combo1.Selected.Result && col2 = Combo2.Selected.Result))

Combo4:
Distinct(myTable; Filter(myTable; col1 = Combo1.Selected.Result && col2 = Combo2.Selected.Result && col3 = Combo3.Selected.Result))

...
...

 

 

But, to have not to repeat each previous filter, I look for how I can refer to the "previous new table".

 

For example:

Combo1:

Distinct(myTable; col1)

 

Combo2:

Distinct(newTable1; col2)

 

Have you an idea ?

 

Thank in advance.

 

Bye.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Charlie,

I think I get what you want to achieve.

Firstly, I want to explain to you that if you do not want to refer to the Filter formula as the data source directly in the Distinct() and you do not want to write more code, I am afraid that there is not such a perfect method.

I think you could create a variable once the Combo Box selected changes, and then you could refer to the variable in the next Distinct() function.

I have a similar test for you, please take a try as below.

1).Combo Box1 Items:

Distinct(Table1,col1)

Combo Box1 OnChange:

Set(FilterTable1,Filter(Table1, col1 = ComboBox1.Selected.Result))

2).Combo Box2 Items:

Distinct(FilterTable1,col2)

Combo Box2 OnChange:

Set(FilterTable2,Filter(Table1, col1 = ComboBox1.Selected.Result && col2 = ComboBox2.Selected.Result))

3).Combo Box3 Items:

Distinct(FilterTable2,col3)

Combo Box3 OnChange:

Set(FilterTable3,Filter(Table1, col1 = ComboBox1.Selected.Result && col2 = ComboBox2.Selected.Result && col3 = ComboBox3.Selected.Result))

4).Combo Box4 Items:

Distinct(FilterTable3,col4)

Regards,

Qi

 

View solution in original post

4 REPLIES 4
v-qiaqi-msft
Community Support
Community Support

Hi @Charlie,

Do you want to filter Combo Box based on the previously filtered result?

Could you please share a bit more about the scenario?

I want to explain to you that Distinct() function needs a required column to evaluate, the formula you provided is a table of the filtered result which could not evaluated in Distinct() function.

I think you have referred to the wrong data source, you should refer to the previously filtered result.

If you want to filter the Combo Box column by column, please check the solution as below.

Combo Box1 Items property:

 

Distinct(Table1,col1)

 

Combo Box2 Items property:

 

Distinct(Filter(Table1, col1 = ComboBox1.Selected.Result),col2)

 

Combo Box3 Items property:

 

Distinct(Filter(Table1, col1 = ComboBox1.Selected.Result && col2 = ComboBox2.Selected.Result),col3)

 

Combo Box4Items property:

 

Distinct(Filter(Table1, col1 = ComboBox1.Selected.Result && col2 = ComboBox2.Selected.Result && col3 = ComboBox3.Selected.Result),col4)

 

Regards,

Qi

Hello @v-qiaqi-msft ,

 

Thank you for your reply and you are right : I want to filter Combo Box based on the previously filtered result.

But I'm really sorry: I did a mistake when I wrote this topic (I believe I was tired 😅). My current code is the same of yours and it works perfectly. Right.

 

Now, I look for a way to be able to not repeat each previously filter. In other language, I think it could be this : 

 

Combo1 Items property:

Distinc(myTable, col1)

 

Combo2 Items property:

Distinct(Filter(myTable, col1 = Combo1.Selected.Result), col2)

 

Combo3 Items property:

Distinct(Filter(Combo2.Items, col2 = Combo2.Selected.Result), col3)

 

...

 

But I don't find how declare the "table" of previously Combo (Combo.Items is not available).

 

I thought to use OnSelected of each combo to create a new collection, but with this method I have to write more code to write less code !!!🤔. It's not a solution for me.

 

I hope I'm more precise and clear :).

 

KR.

 

Charlie.

Hi @Charlie,

I think I get what you want to achieve.

Firstly, I want to explain to you that if you do not want to refer to the Filter formula as the data source directly in the Distinct() and you do not want to write more code, I am afraid that there is not such a perfect method.

I think you could create a variable once the Combo Box selected changes, and then you could refer to the variable in the next Distinct() function.

I have a similar test for you, please take a try as below.

1).Combo Box1 Items:

Distinct(Table1,col1)

Combo Box1 OnChange:

Set(FilterTable1,Filter(Table1, col1 = ComboBox1.Selected.Result))

2).Combo Box2 Items:

Distinct(FilterTable1,col2)

Combo Box2 OnChange:

Set(FilterTable2,Filter(Table1, col1 = ComboBox1.Selected.Result && col2 = ComboBox2.Selected.Result))

3).Combo Box3 Items:

Distinct(FilterTable2,col3)

Combo Box3 OnChange:

Set(FilterTable3,Filter(Table1, col1 = ComboBox1.Selected.Result && col2 = ComboBox2.Selected.Result && col3 = ComboBox3.Selected.Result))

4).Combo Box4 Items:

Distinct(FilterTable3,col4)

Regards,

Qi

 

View solution in original post

Charlie
Helper I
Helper I

Hello,

Thank a lot to your help. So I stay as it.

Bye.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (35,919)