Forum Discussion

MP_123's avatar
MP_123
Icon for Microsoft Employee rankMicrosoft 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's avatar
    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

     

MP_123's avatar
MP_123
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

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's avatar
konstantinos
Icon for Memorable Member rankMemorable Member
10 years ago

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]) )