Forum Discussion
Post Patronrelationship with duplicates
Trying to figure out how to handle this.
I am doing row level security for the managers of stores and corporate. Want the people who should see their store to be able to see their store or stores if multiple. There are multiple people who can see each store and they crossover to see other stores as well.
First in trying to create the relationship, it makes sense for both tables (user and sales) to have duplicates but I do a relationship with the tables on store # and it won't work.
What is the solution? and thanks in advance
| USER TABLE | |
| Name | Manager |
| [email protected] | Store 1 |
| [email protected] | Store 1 |
| [email protected] | Store 2 |
| [email protected] | Store 3 |
| [email protected] | Store 1 |
| SALES TABLE | ||
| Month | Store | Sales |
| Feb | Store 1 | 100 |
| March | Store 1 | 80 |
| April | Store 1 | 60 |
| Feb | Store 2 | 40 |
| March | Store 2 | 12 |
| April | Store 2 | 13 |
| Feb | Store 3 | 50 |
| March | Store 3 | 13 |
| April | Store 3 | 20 |
1 Reply
v-lili6-msftCommunity Support
HI, electrobrit
After my test, you need to add a Store fact table as the middle table to create the relationship for the two table
use DISTINCT Function and UNION Function to create the Store fact table
Store = DISTINCT(UNION(VALUES(SALES[Store]),VALUES(USER[Manager])))
Then create the relationship as below:
Best Regards,
Lin