can't model relationship in power bi

[ovonel]

I am not sure how to model this.

I have 3 tables.

• Fact
• Contract
• Clients

Contract	Manager1	Manager2
1	Peter	Rob
2	Peter	Tim
3	Tim	James
4	Peter	James

Client	C. Manager1
1	P
2	NM

Fact:

Sale	Contract	Client
10	1	1
10	1	2
10	2	2
10	3	2
10	4	2

Then, the access with RLS will be based on contract. If you are manager1 or manager2 for that contract you shall see it (also if you are c. manager1  on that row you shall see it too).

So, for the table for access,  I will do an Unpivot like:

SqlTable
Contract	Client
1	1	Manager1
1	1	Manager2
1	1	C. Manager1
1	2	Manager1
1	2	Manager2
1	2	C. Manager1
2	2	Manager1
2	2	Manager2
2	2	C. Manager1
3	2	Manager1
3	2	Manager2
3	2	C. Manager1
4	2	Manager1
4	2	Manager2
4	2	C. Manager1

I have tried this model but it doesnt work:

Is there a way to achieve this functionality?

[Tutu_in_YYC · Accepted solution]

Try combining Client and Contract into 1 table e.g ClientContract, and create a composite key that consist of client and contract ID. Use that to connect to Fact ( it means you have to have the same composite key in the Fact )