How to deal with many to many relationships?

[Anonymous]

Hi, 

I would like to know the most efficient way of setting up the following relationship. I've been having troubles in understanding how to model a situation where multiple staff members work at multiple stores.

My tables are as follows

Store table:

StoreId	Store Name
1	A
2	B
3	C
4	D
5	E
6	F

Staff table: 

Where Staff Id is just a unique record identifier, staff code represents a unique staff member, store Id represents which store the staff member has worked at. Evidently, a single staff code can appear many times for multiple dealers.

Staff Id	Staff Code	Store Id	Staff Name
1	1111	1	Jeff
2	1111	2	Jeff
3	2222	2	John
4	3333	3	James
5	3333	6	James
6	4444	4	Ben
7	5555	5	Kevin
8	6666	6	Maria

Sales table: 

SalesId	Staff Id	Staff Code	Store Id	Amount
1	1	1111	1	$50
2	2	1111	2	$40
3	1	1111	1	$60
4	3	2222	2	$25
5	8	6666	6	$60
6	5	3333	6	$25
7	4	3333	3	$95
8	6	4444	4	$90

How do I set up these relationships so that I can use a slicer to select a store name, and display the staff members for that store, and their total sales amount only when they worked at that store? I have had troubles with inactive relationships and bi-directional filtering... 

Appreciate any help 🙂

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

Supplement @FarhanAhmed 's reply.

Store table and Sales table based on Store ID.

Staff table and Sales table based on Staff ID.

Then create a table visual and a slicer like this,

Please pay attention to the fields selected for each Visual.

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[FarhanAhmed]

Since your Sales table already contains StoreID and Staff Code you can create the following relationship

Store-Sales -->1-Many (StoreId)

Staff-Sales --> 1-Many (StaffCode)

Use the Store from Store Table (Either in Slicer or Visual)

Use Staff from Staff table (Either in Slicer or Visual)

Use Sales Value from Sales Table.

[amitchandak]

@Anonymous , it is it import mode , Create a contact column in both tables

Key = [Store Id] & "-"& [Staff Code]

And join on that. Assume at that time Staff to sales is One to Many.

If not you have to create a bridge Table

https://www.seerinteractive.com/blog/join-many-many-power-bi/

Other ways to take out a table from existing table (Power Query)

https://youtu.be/kU2M1LmNvNo
https://youtu.be/vHuhbvYCiNc