Forum Discussion

Microsoft Employee

10 years ago

Solved

implementing where on slicer

hi, i have a slicer with products for example: milk bread jam chocolate in the DB, i could have the four options but also combinations of them: milk bread jam chocolate milk,jam bread...

Need Help

Vvelarde
10 years ago
Hi MP_123

Hi, Assuming that you only have 1 table with single and combinations together.

AllProducts

Products      Amount
Bread              5
Chocolate       7
Jam                 8
Milk                10
Milk,Jam        12
Bread, Milk     3
Chocolate, Jam 4
Bread                3

etc

1. Create a Table "Single-Products" with your 4 single products

Product   Level
Bread        1
Chocolate 1
Milk          1
Jam          1
Make the relationship between "AllProducts" and "Single-Products"

2. Create a calculated column in Table "AllProducts"

Level = if(RELATED('Single-Products'[Level])=1;1;2)

3. Create a New Table

Products-Level1 = SUMMARIZE(FILTER('AllProducts';'AllProducts'[Level]=1);'AllProducts'[Products])

4. Create a measure in Table "AllProducts"

Products-Contain-ProductSelected = If(SEARCH(VALUES('Products-Level1'[Products]);VALUES('AllProducts'[Products]);1;0)=1;1;0)

5. Put a Slicer with Products-Level1 and Field Products

6. Put a Table Visuals with AllProducts with the fields: Products and Amount

And Filter Level Visual : Products-Contain-ProductSelected =1

I hope this help you