Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ovonel
Post Prodigy
Post Prodigy

can't model relationship in power bi

I am not sure how to model this.

 

I have 3 tables.

  • Fact
  • Contract
  • Clients

 

ContractManager1Manager2
1PeterRob
2PeterTim
3TimJames
4PeterJames

 

 

ClientC. Manager1
1P
2NM

 

 

Fact:

SaleContractClient
1011
1012
1022
1032
1042

 

 

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  
ContractClient 
11Manager1
11Manager2
11C. Manager1
12Manager1
12Manager2
12C. Manager1
22Manager1
22Manager2
22C. Manager1
32Manager1
32Manager2
32C. Manager1
42Manager1
42Manager2
42C. Manager1

 

 

I have tried this model but it doesnt work:

ovonel_0-1660749124648.png

 

 

Is there a way to achieve this functionality?

1 ACCEPTED SOLUTION
Tutu_in_YYC
Resident Rockstar
Resident Rockstar

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 )


View solution in original post

1 REPLY 1
Tutu_in_YYC
Resident Rockstar
Resident Rockstar

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 )


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors