implementing where on slicer

[MP_123]

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, milk
• etc.

is someone have an idea to implement the following:

show only the four on t he slicer, but if i select 'milk' power bi will slice with the milk combinations also.

someone?

thanks a lot

[Vvelarde · Accepted solution]

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

[MP_123]

hi @Vvelarde

i tried to implement your solution and it works!

but one thing. the measure work with table visual only if i add the products as well , i can't use card visual for example

do you have an idea how to solve it?

thanks a lot!

[Sean]

@MP_123 You can create a duplicate table in the Query Editor and use it for the Slicer!

See results in the image and follow the instructions if you'd like to try it...

Since this is an M solution the right thing to do is include @ImkeF - just in case we need to add a step or 2 

Thanks!

[ankitpatira]

@MP_123 easy way to do would be to not use slicer but report filter instead and in filter go to advanced filtering and select contains. Now when you filter by 'milk', any products containing milk in the name would also be included.

[MP_123]

@ankitpatira

you're right, but the point is to let the user select first, i need it to be generic

[ankitpatira]

@MP_123 your users can also set filters within your report when you share with them.

[MP_123]

@ankitpatirahow do you share reports (not dashboards)

[ankitpatira]

@MP_123 you share dashboards not reports. but when shared user clicks on the dashboard they get to see underlying report.

[MP_123]

@ankitpatira and also, as i know so far, in the report filters you can only define measures

[SamLester]

I haven't tried it out yet, but would the Hierarchy Slicer in custom visuals work for this?

https://app.powerbi.com/visuals/

Thanks,
Sam Lester (MSFT)

[MP_123]

thanks!

it actually get me closer to my desired result but not enough

with the hierarchy slicer i can't select more than one option (if options are not under the same parent)

and also can't collaps leaves - estheticaly not so pretty:)

thanks again

[konstantinos]

@MP_123  Really interesting problem.

The solution I come up might not be ideal due to performance issues but major problem is that I cannot sum the total amounts ( you will understand later ).  I am posting anyway since someone can optimize or modify it to work correct.

I tried only work with formula as there might be options with data modelling.

If you need to filter a table the measure works ( exept totals ) , credit to @powerpivotpro for the post containsx 

http://www.powerpivotpro.com/2014/01/containsx-revisited-what-was-the-match/

Table with unique items called 'Items'

OneItem
milk
jam
bread

Table with mixed items called 'Many'

Mixed	Amount
milk,jam	1
jam,bread	1
jam,milk	1
bread,milk	1
milk,bread	1
jam,milk,bread	1

No relantionship between tables

We visualize a table with Many[Mixed] on rows and Many[Amount] as values, also add slicer from Items table

*Limitation 1 = Have to select only one Item in slicer.

Create new measure 

ContainsItem = 
IF (
    HASONEVALUE ( Items[OneItem] );
    SUMX (
        FILTER (
            Many;
            CONTAINS (
                ADDCOLUMNS (
                    SUMMARIZE ( Many; Many[Mixed]; Many[Amount] );
                    "WhatItem"; FIRSTNONBLANK (
                        FILTER (
                            VALUES ( Items[OneItem] );
                            SEARCH ( Items[OneItem]; Many[Mixed]; 1; 0 )
                        );
                        1
                    )
                );
                [WhatItem]; VALUES ( Items[OneItem] )
            )
        );
         Many[Amount] 
    )
)

Then add the [ContainsItem] measure in table..Filter on 'Jam'

and the table shows only rows containing Jam

Filter on 'Bread', different rows

As you noticed the totals don't work correct. I was trying to work it out but run out of time now. 

Hopefully someone of the community with more knowledge can help.

Another try I made with variables & INTERSEPT ( with the same results ) is

ContainsItem3 = 
IF (
    HASONEVALUE ( Items[OneItem] );
    
    VAR TableWithMatchItem =
        FILTER (
            Many;
            CONTAINS (
                ADDCOLUMNS (
                    SUMMARIZE ( Many; Many[Mixed] );
                    "WhatItem"; FIRSTNONBLANK (
                        FILTER (
                            VALUES ( Items[OneItem] );
                            SEARCH ( Items[OneItem]; Many[Mixed]; 1; 0 )
                        );
                        1
                    )
                );
                [WhatItem]; VALUES ( Items[OneItem] )
            )
        )
    RETURN
        SUMX(INTERSECT ( Many; TableWithMatchItem );Many[Amount]) )