Forum Discussion

ABCD1234's avatar
ABCD1234
Regular Visitor
9 years ago
Solved

Slicer for multiple tables

Hi,

 

I have two tables.

>Table1                                                    

ProductValue
AB 12312
AB 11123
AC 19934
AC 20045
AD 99956

> Table2

ProdSome Value
AB 999100
AC 321200
AD 893300
AD 223400

 

I created a new column "Type" for Table1 as Type = if(LEFT(Table1[Product],2)="AB","Type1",if(LEFT(Table1[Product],2)="AC","Type2",if(left(Table1[Product],2)="AD","Type3","None")))However, the slicer i created using this new column "Type" doesnt filter visuals created using Table2. I tried to define a relationship between the two tables. However, i couldn't do that because the entries in "Product" in Table1 need not necessarily be entries in "Prod" in Table2. The key point here is that all products having the same first two letters are of the same type and i want to filter by this type. Does anyone know how this can be done in Powerbi?

 

Thanks in advance! 

  

Need Help
  • DoubleJ's avatar
    DoubleJ
    9 years ago

    Then you can merge the tables and keep both columns. Following a way that might work in your case.

     

    1.  Let's say your 2 tables are as follows

      Table1Table2

    2.  In Query editor: Select "Merge Queries" - "Merge Queries as New"



    3. Select the Product Column in both tables and select "Full Outer" Join


    4. Click on the double headed arrow 

      This should lead to this table:




    5. Select "Add Column" - "Conditional Column" and set up a logic that combines the 2 product columns


      Result:



    6. Clean up your table by replacing null values, deleting superfluous columns etc.

     

     

    Hope this helps!
    JJ

3 Replies

    • ABCD1234's avatar
      ABCD1234
      Regular Visitor
      9 years ago

      The problem is that the values in both the tables are not related, for example the "Value" column in the first table could be Date and the "Some Value" in the second table could be a decimal. 

      • DoubleJ's avatar
        DoubleJ
        Solution Supplier
        9 years ago

        Then you can merge the tables and keep both columns. Following a way that might work in your case.

         

        1.  Let's say your 2 tables are as follows

          Table1Table2

        2.  In Query editor: Select "Merge Queries" - "Merge Queries as New"



        3. Select the Product Column in both tables and select "Full Outer" Join


        4. Click on the double headed arrow 

          This should lead to this table:




        5. Select "Add Column" - "Conditional Column" and set up a logic that combines the 2 product columns


          Result:



        6. Clean up your table by replacing null values, deleting superfluous columns etc.

         

         

        Hope this helps!
        JJ