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

     

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

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