Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am not sure how to model this.
I have 3 tables.
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?
Solved! Go to 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 )
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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.