Dynamic row level security for multiple tables

[Anonymous]

Hi,

I have a data model in PowerBI with the following setup.

1) OIS is our main table with many transactional records for all our customers.

2) OIS table direct links to OISPermissionGroup with 1-Many relationship

3) All the tables inside the PINK section (ie. OISPermissionGroup, PermissionGroup, UserPermissionGroup, User) are the tables that we are using to identify the user permission. In other words, it allows Power BI to filter the records that are associated to different customers. For example, CustomerA login should only see CustomerA’s data. CustomerB login should only see CustomerB’s data…..and so on. By using this concept, CustomerB should not see other customer’s data in their own login.

4) Same time, I did the following settings in the Manage Roles screen under the OIS table with a DAX expression to filter specific user records for a 1-Many relationship between OIS and Hence, I can easily view specific records in OIS table from different user’s perspective by changing int(username()) in the expression to a particular userid and choose “View as Roles” by user.

Here is the DAX I am using in the OIS table:

int(username()) in SELECTCOLUMNS(RelatedTable(OrderedItemSummaryPermissionGroup), "User", [UserId])

5) Table DTD/DTP/PTD/PTP are shipment status (“Status”) tables calculated from OIS by using DAX expression based on certain conditions (ie. SUMMARIZE function from OIS table). These 4 tables are arranged in the same format but just the calculation is slightly different. There is a common calculated column in these 4 tables called “CustomerId”, “Mode” and “Transit Time” which will be used later.

6) Combine table is a calculated table that consolidates all the calculated records from DTD/DTP/PTD/PTP by using UNION and SELECTCOLUMNS expression.

7) In the Combine table, I created a new column called “Average Target Days” to calculate the average days per each of the DTD/DTP/PTD/PTP table per “CustomerId” per “Status” per “Mode” which is calculated correctly using DAX expression.

(8) Now, I want each of our customer to view their own “Average Target Days” based on their own user permission. I tried to add the same DAX expression from step (4) into Combine table but it didn’t work. Basically, I want to create a dynamic row level security with multiple tables just like what I have in the diagram. The DAX formula is not working if I put that formula into the Combine

Can anyone help on this issue?

Much thanks!!

Best regards,

Emily

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

You can create a new table to store the user's mailbox and ID. please refer to this blog:https://radacad.com/dynamic-row-level-security-with-power-bi-made-simple 

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