filter using combination of multiselect options from combo box

[pabitra]

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 1	Test 1
Package 1	Test 3
Package 1	Test 10
Package 1	Test 20
Package 1	Test 29
Package 1	Test 7
Package 1	Test 23
Package 1	Test 90
Package 1	Test 27
Package 2	Test 10
Package 3	Test 20
Package 4	Test 29
Package 5	Test 7
Package 6	Test 23
Package 7	Test 90
Package 8	Test 27

[v-monli-msft]

Hi @pabitra ,

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

Regards,

Mona

[pabitra]

Data is in excel.

[PowerAddict]

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. 

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

Hardit(Haman)

[pabitra]

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.

[PowerAddict]

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

[PowerAddict]

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. 

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

Hardit(Haman)

[pabitra]

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

Please use this and check.

Thanks

[pabitra]

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.

[PowerAddict]

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)