cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

filter using combination of multiselect options from combo box

There are 2 columns with multiple tests mapped to multiple packages.

I select distinct values of multiple tests in a combo box and want to select a package which consists of these multiple tests.

 

For distinct tests-

Sort(Distinct(Table1,Tests),Result)

 

For relevant packages-

Distinct(Filter(Table1,Tests in ComboBox1.SelectedItems.Result),Package)

 

But, the 2nd result gives me the package for last selection(not combination of multiple sleections in combo box)

How can i slelect a combination of all tests from 1st combo box and then select a package with all those tests.

 

FYI- Every package consists of 15-20 tests arranged in random order

Data is arranged as-

Package 1Test 1
Package 1Test 3
Package 1Test 10
Package 1Test 20
Package 1Test 29
Package 1Test 7
Package 1Test 23
Package 1Test 90
Package 1Test 27
Package 2Test 10
Package 3Test 20
Package 4Test 29
Package 5Test 7
Package 6Test 23
Package 7Test 90
Package 8Test 27
13 REPLIES 13
Highlighted
Community Support
Community Support

Re: filter using combination of multiselect options from combo box

Hi @pabitra ,

 

What is the connection of this table? Is it an excel table or SharePoint list?

 

Regards,

Mona

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: filter using combination of multiselect options from combo box

Data is in excel.

 

Highlighted
Super User
Super User

Re: filter using combination of multiselect options from combo box

For testing purposes, I have created a collection like this (instead of using excel but it should be the same):

ClearCollect(Excel,{Package:"Package 1",Test:"Test 1"},{Package:"Package 1",Test:"Test 10"},{Package:"Package 2",Test:"Test 21"},{Package:"Package 2",Test:"Test 25"},{Package:"Package 3",Test:"Test 31"})

 

Then my 1st combo box items property is set as (exactly as yours): Sort(Distinct(Excel, Test), Result)

 

Then my 2nd combo box items property is also set as the way you have set: Distinct(Filter(Excel,Test in ComboBox12.SelectedItems.Result),Package)

 

I have also set the DefaultSelectedItems property of the 2nd combo box with the same formula: Distinct(Filter(Excel,Test in ComboBox12.SelectedItems.Result),Package)

 

It works perfectly fine for me, at least based on what I have understood about your requirement. I am attaching a file, please let me know if this is what you are looking for. 

 

Combo.gif

 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

Highlighted
Frequent Visitor

Re: filter using combination of multiselect options from combo box

Hi @PowerAddict ,

 

Thanks for your reply.

But as I have mentioned in previous comments, its not what I am looking for.

 

This search gives me packages with OR combination of tests but I want AND combination.

If the selected tests are all present in a package, it will show the package.

 

For example, lets say you select test10 and test21, it should select null(AND combination of tests) rather than both packages- package1 and package2(OR combination of tests).

I should get package1 when I select test1 and test10 according to your data.

 

Hope I was able to explain the problem statement.

 

Highlighted
Super User
Super User

Re: filter using combination of multiselect options from combo box

Got it. Makes perfect sense now. I will get back to you with this.
Highlighted
Super User
Super User

Re: filter using combination of multiselect options from combo box

Here you go: 

 

Combo box 1 (same as before): 

Items property: Sort(Distinct(Excel, Test), Result)

 

Combo box 2

Items property: Distinct(Excel,Package)

DefaultSelectedItems: If(CountRows(Distinct(Filter(Excel,Test in ComboBox12.SelectedItems.Result),Package))=1, Distinct(Filter(Excel,Test in ComboBox12.SelectedItems.Result),Package))

 

Basically what I am doing here is checking for distinct package values (just like before) BUT also checking for count at the same time. If the count is 1, only then the combo box defaults to that package. 

 

Same as before, if you only want the combo box to have the filtered package values instead of default selected items, you can use the above DefaultSelectedItems expression for the Items property of the combo box. 

 

Here is how it works, let me know if this is in line with your expectation. 

 

Combo2.gif

 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

Highlighted
Frequent Visitor

Re: filter using combination of multiselect options from combo box

Hi @PowerAddict ,

 

This method works for only small dataset that we were using as there is only one package which gives count as 1.

I have a larger dataset of around 400 packages where each package has 20-25 tests which gives me 5000+ rows.

 

This does not serve the purpose for that dataset.

 

 

Use this one.

Package                       Test

package01-01test 4
package01-01test 26
package01-01test 9
package01-01test 49
package01-01test 17
package01-01test 30
package01-01test 26
package01-01test 40
package01-01test 33
package01-01test 24
package01-01test 29
package01-01test 16
package01-01test 46
package01-01test 7
package01-01test 33
package01-01test 46
package01-01test 26
package02-01test 45
package02-01test 33
package02-01test 5
package02-01test 30
package02-01test 25
package02-01test 23
package02-01test 26
package02-01test 4
package02-01test 22
package02-01test 38
package02-01test 22
package02-01test 13
package02-01test 8
package02-01test 7
package02-01test 10
package02-01test 48
package02-01test 14
package02-01test 26
package02-01test 26
package02-01test 20
package02-01test 28
package02-01test 38
package02-01test 45
package02-01test 20
package02-01test 33
package02-01test 22
package02-01test 17
package02-01test 28
package02-02test 21
package02-02test 9
package02-02test 17
package02-02test 36
package02-02test 12
package02-02test 37
package02-02test 13
package02-02test 7
package02-02test 20
package02-02test 6
package02-02test 24
package02-02test 12
package02-02test 23
package02-02test 31
package02-02test 26
package02-02test 4
package02-02test 29
package02-02test 41
package02-02test 18
package02-02test 36
package02-02test 36
package02-02test 33
package02-02test 37
package02-02test 22
package02-02test 45
package02-02test 25
package02-02test 50
package02-02test 36
package02-02test 10
package02-02test 13
package02-02test 40
package02-02test 19
package02-02test 9
package02-03test 11
package02-03test 50
package02-03test 30
package02-03test 48
package02-03test 29
package02-03test 34
package02-03test 4
package02-03test 19
package02-03test 48
package02-03test 20
package02-03test 24
package02-03test 18
package02-03test 28
package02-03test 21
package02-03test 14
package02-03test 5
package02-03test 15
package02-03test 39
package02-03test 30
package02-03test 21
package02-03test 27
package02-03test 25
package02-03test 1
package02-03test 24
package02-03test 20
package02-03test 12
package02-03test 9
package02-03test 1
package02-03test 23
package02-03test 8
package02-03test 41
package02-03test 9
package02-03test 24
package02-03test 6
package02-03test 16
package02-03test 37
package01test 2
package01test 18
package01test 48
package01test 1
package01test 28
package01test 11
package01test 22
package01test 50
package01test 29
package01test 46
package01test 13
package01test 13
package01test 1
package01test 49
package01test 12
package01test 6
package01test 36
package01test 23
package01test 33
package01test 41
package01test 21
package01test 15
package01test 40
package01test 38
package01test 34
package01test 27
package01test 22
package01test 18
package01test 43
package01test 46
package01test 21
package01test 21
package01test 38
package01test 16
package01test 40
package01test 45
package01test 8
package01test 40
package01test 17
package01test 21
package01test 37
package01test 30
package01test 31
package01test 20
package01test 29
package01test 31
package01test 10
package01test 10
package01test 25
package01test 2
package01test 37
package01test 15
package01test 45
package01test 20
package01test 32
package01test 1
package01test 21
package01test 7
package01test 27
package01test 17
package01test 24
package01test 12
package01test 3
package01test 21
package01test 34
package01test 31
package01test 35
package01test 28
package01test 32
package01test 15
package01test 32
package01test 6
package01test 18
package01test 1
package01test 34
package01test 15
package01test 45
package01test 14
package01test 44
package02-01test 43
package02-01test 6
package02-01test 30
package02-01test 48
package02-01test 41
package02-01test 49
package02-01test 10
package02-01test 39
package02-01test 30
package02-01test 18
package02-02test 16
package02-02test 14
package02-02test 21
package02-02test 50
package02-02test 2
package02-02test 5
package02-02test 32
package02-02test 15
package02-02test 2
package02-02test 32
package02-02test 2
package02-02test 44
package02-03test 37
package02-03test 18
package02-03test 30
package02-03test 48
package02-03test 40
package02-03test 3
package02-03test 27
package02-03test 29
package02-03test 27
package02-04test 37
package02-04test 16
package02-04test 18
package02-04test 33
package02-04test 28
package02-04test 17
package02-04test 25
package02-04test 39
package02-04test 1
package02-04test 11
package02-04test 26
package02-04test 50
package02-04test 19
package02-04test 19
package02-04test 38
package02-04test 22
package02-04test 27
package02-04test 14
package02-04test 4
package02-04test 24
package02-04test 19
package02-04test 1
package02-04test 45
package02-04test 27
package02-04test 12
package02-04test 40
pacPackageTest
package01-01test 4
package01-01test 26
package01-01test 9
package01-01test 49
package01-01test 17
package01-01test 30
package01-01test 26
package01-01test 40
package01-01test 33
package01-01test 24
package01-01test 29
package01-01test 16
package01-01test 46
package01-01test 7
package01-01test 33
package01-01test 46
package01-01test 26
package02-01test 45
package02-01test 33
package02-01test 5
package02-01test 30
package02-01test 25
package02-01test 23
package02-01test 26
package02-01test 4
package02-01test 22
package02-01test 38
package02-01test 22
package02-01test 13
package02-01test 8
package02-01test 7
package02-01test 10
package02-01test 48
package02-01test 14
package02-01test 26
package02-01test 26
package02-01test 20
package02-01test 28
package02-01test 38
package02-01test 45
package02-01test 20
package02-01test 33
package02-01test 22
package02-01test 17
package02-01test 28
package02-02test 21
package02-02test 9
package02-02test 17
package02-02test 36
package02-02test 12
package02-02test 37
package02-02test 13
package02-02test 7
package02-02test 20
package02-02test 6
package02-02test 24
package02-02test 12
package02-02test 23
package02-02test 31
package02-02test 26
package02-02test 4
package02-02test 29
package02-02test 41
package02-02test 18
package02-02test 36
package02-02test 36
package02-02test 33
package02-02test 37
package02-02test 22
package02-02test 45
package02-02test 25
package02-02test 50
package02-02test 36
package02-02test 10
package02-02test 13
package02-02test 40
package02-02test 19
package02-02test 9
package02-03test 11
package02-03test 50
package02-03test 30
package02-03test 48
package02-03test 29
package02-03test 34
package02-03test 4
package02-03test 19
package02-03test 48
package02-03test 20
package02-03test 24
package02-03test 18
package02-03test 28
package02-03test 21
package02-03test 14
package02-03test 5
package02-03test 15
package02-03test 39
package02-03test 30
package02-03test 21
package02-03test 27
package02-03test 25
package02-03test 1
package02-03test 24
package02-03test 20
package02-03test 12
package02-03test 9
package02-03test 1
package02-03test 23
package02-03test 8
package02-03test 41
package02-03test 9
package02-03test 24
package02-03test 6
package02-03test 16
package02-03test 37
package01test 2
package01test 18
package01test 48
package01test 1
package01test 28
package01test 11
package01test 22
package01test 50
package01test 29
package01test 46
package01test 13
package01test 13
package01test 1
package01test 49
package01test 12
package01test 6
package01test 36
package01test 23
package01test 33
package01test 41
package01test 21
package01test 15
package01test 40
package01test 38
package01test 34
package01test 27
package01test 22
package01test 18
package01test 43
package01test 46
package01test 21
package01test 21
package01test 38
package01test 16
package01test 40
package01test 45
package01test 8
package01test 40
package01test 17
package01test 21
package01test 37
package01test 30
package01test 31
package01test 20
package01test 29
package01test 31
package01test 10
package01test 10
package01test 25
package01test 2
package01test 37
package01test 15
package01test 45
package01test 20
package01test 32
package01test 1
package01test 21
package01test 7
package01test 27
package01test 17
package01test 24
package01test 12
package01test 3
package01test 21
package01test 34
package01test 31
package01test 35
package01test 28
package01test 32
package01test 15
package01test 32
package01test 6
package01test 18
package01test 1
package01test 34
package01test 15
package01test 45
package01test 14
package01test 44
package02-01test 43
package02-01test 6
package02-01test 30
package02-01test 48
package02-01test 41
package02-01test 49
package02-01test 10
package02-01test 39
package02-01test 30
package02-01test 18
package02-02test 16
package02-02test 14
package02-02test 21
package02-02test 50
package02-02test 2
package02-02test 5
package02-02test 32
package02-02test 15
package02-02test 2
package02-02test 32
package02-02test 2
package02-02test 44
package02-03test 37
package02-03test 18
package02-03test 30
package02-03test 48
package02-03test 40
package02-03test 3
package02-03test 27
package02-03test 29
package02-03test 27
package02-04test 37
package02-04test 16
package02-04test 18
package02-04test 33
package02-04test 28
package02-04test 17
package02-04test 25
package02-04test 39
package02-04test 1
package02-04test 11
package02-04test 26
package02-04test 50
package02-04test 19
package02-04test 19
package02-04test 38
package02-04test 22
package02-04test 27
package02-04test 14
package02-04test 4
package02-04test 24
package02-04test 19
package02-04test 1
package02-04test 45
package02-04test 27
package02-04test 12
package02-04test 40
package02-04test 6
kage02-04
test 6

 

Please use this and check.

Thanks

 
 
 

 

 

Highlighted
Frequent Visitor

Re: filter using combination of multiselect options from combo box

Hi @PowerAddict 

 

This works for a small dataset which we have used.

As there are only 2 tests, counter gives a value of 1 package.

 

But, I have a large dataset which contains around 500 packages with 20-25 tests in each package.

 

Please try with the attached dataset.

Highlighted
Super User
Super User

Re: filter using combination of multiselect options from combo box

Its not about the size of the dataset. Your dataset is different. It wasn't clear from your previous example that one test could be in multiple packages. That changes things a bit. Will work on it and let you know. 

 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Users online (5,367)