One slicer for two tables (M:M)

[badgerdds]

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!

[CheenuSing · Accepted solution]

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]

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

Both solutions worked xx

[v-chuncz-msft]

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