Forum Discussion

badgerdds's avatar
badgerdds
New Member
8 years ago
Solved

One slicer for two tables (M:M)

Hi All

 

Hoping someone can help me out - I'm very new to PowerBI so please bare with me! :)

 

I have two tables, neither of the two tables have a singluar column that holds unique values, but they do share the same column of data "Business Area". However, what I would like to be able to do, is for both tables to update off once slicer for "Business Area". PowerBI will not let me make a relationship between them using "Business Area" as this is not a unique value.

 

 

 

you can see above my two tables...

 

 

 

 

 

my rather crude example :) ... the slicer in the middle is from table 1 "Business Area" - when i change this it will update the graph for table 1 - which is great - but i'd like it to also update table 2 graph to show the Count of "Leavers". both Graphs are just a count of Business Area

 

I hope I'm making sense!?

 

My thanks for your help in advance!

  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi badgerdds

     

    Try the following

     

    1. Create a Table of Business Areas as the only column.

    2. Join this table to bothe Table1 and Table 2 as 1:M relationship - Both

    3. Use the table at step 1 for slicer

    4. Now check the output of the two graphs.

     

     

    If this works for you please accept this as a solution and also give KUDOS.

     

    Cheers

     

    CheenuSing

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi badgerdds

     

    Try the following

     

    1. Create a Table of Business Areas as the only column.

    2. Join this table to bothe Table1 and Table 2 as 1:M relationship - Both

    3. Use the table at step 1 for slicer

    4. Now check the output of the two graphs.

     

     

    If this works for you please accept this as a solution and also give KUDOS.

     

    Cheers

     

    CheenuSing

     

    • badgerdds's avatar
      badgerdds
      New Member
      8 years ago

      Thank you both very much for your response! - that did the trick :)

       

      Both solutions worked xx

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    badgerdds,

     

    Drag column from the following calculated table and build relationship on it.

    Table =
DISTINCT (
    UNION (
        SELECTCOLUMNS ( Table1, "Business Area", Table1[Business Area] ),
        VALUES ( Table2[Business Area] )
    )
)