Exposing Data in a Table via Calculated Columns using 1-*-1 relationship via bridging table

[T0_0H]

Ask:
Look to create a calculated column in the 'Client Details' table that brings the Privileged Information from the table 'Client Details - Privileged Information'

e.g. columns 'Willingness to Engage', 'Strategy developed' & 'CEO Details'

Current Structure

Blue: Keys between 'Client Detail' &  'Bridge Client Details Privileged Information'

Green: Key between  'Bridge Client Details Privileged Information' &  'Client Details - Privileged Information'

Dotted Purple: Example of use of RELATED() to bring into from 'Client Details - Privileged Information' into  'Bridge Client Details Privileged Information'

We are using a bridging table as a security table to dictate what information a user can see about a client.
This table is has one row per user per company. The value in 'Bridge Client Details Privileged Information'[Secured key] links to 'Client Details - Privileged Information'[PI secure Client Details]

Issues:
This isn't a direct one to one mapping. We are implementing a bridging table. To get the pretend 1-1-1 relationship we want we use RLS on the Bridging table. [Staff Username] = USERNAME ()
This RLS is implement at the time of render or access by the user.
It checks the user name and only lets the user 'see' rows in the bridging table where the value in the Staff_Username field matches the username of the logged in user.
This works as we have one row per user per company. So once the RLS kicks and the fields are used with a relevant measure it behaves as a one to one to one mapping.

Currently (see data example at end of post)

• All users can see the information in the 'Client Details' table
• For Company 1, User B & User C will see [Not on File] for 'CEO Details'
• For Company 1, User A & User D will see [Privileged Information]

I have been asked to make the model simpler for the end users. The ask is to hide the 'Client Details - Privileged Information' table and expose it's data via calculated columns in the 'Client Details' table.

I'm not sure how to go about this as the RLS is needed to know what the user can see, but I don't think the RLS would affect the Calculated column.
Is there a function that will compute the value in the calculated column based on the bridging table after its ben filtered by RLS?
 

I have tried solution using

• Lookup() - Can't use Username() functions in a calculated column
• Related() -  works with one to many relationships. I could use it to bring the fields from the 'Client Details - Privileged Information' to  'Bridge Client Details Privileged Information' but I have not found a way to bring them the next step from  'Bridge Client Details Privileged Information' to 'Client Details'.

Could anyone advise is what is being asked possible?
I'm quit new to tabular modelling & DAX, so if the initial bridging solution we are using is a problem in itself and there is a more elegant way to achieve this end goal I'd hugely appreciate being pointed towards documentation that would help.
Thank you.

Additional Information
We are using Microsoft Analysis Services for SQL 2017, and the latest PowerBI Onprem (Jan 24)

Implementation of relationships

Relationship	Cross Filtering	Security Filtering
'Client Details' (1) and Bridging (m) From: 'Bridge Client Details Privileged Information' To: 'Client Details'	Both Direction	One Direction RLS on 'Client Details' will be passed to 'Bridge Client Details Privileged Information' RLS on 'Bridge Client Details Privileged Information' WILL NOT be passed to 'Client Details'
'Client Details - Privileged Information'  (1) and Bridge (m) From: 'Bridge Client Details Privileged Information' To: 'Client Details - Privileged Information'	Both Direction	Both Direction

Example of Data

[v-jianpeng-msft]

Hi, @T0_0H 

You are trying to implement row-level security (RLS) to control access to data in a tabular model. You mentioned that you are using bridging tables and RLS to create a one-to-one relationship between users, companies, data.
From what I understand, it is not possible to use RLS directly in calculated columns. However, you may be able to achieve the desired result by combining RLS with DAX expressions. Here's one possible solution:
You create a metric that makes the "USERPRINCIPALNAME" or "USERNAME" function determine the current user, and then use this information in conjunction with the "CALCULATETABLE" or "FILTER" function to filter the data in the "Client" Details - a privilege information table based on user access. You can then use that measure in a calculated column to display the appropriate data to the user.

You can learn more about how to use USERNAME , USERPRINCIPALNAME in DAX expressions through the links below.

Power BI Security Roles in DAX: https://www.sqlbi.com/articles/reading-active-power-bi-security-roles-in-dax/

Customizing default values for each user in Power BI reports: https://www.sqlbi.com/articles/customizing-default-values-for-each-user-in-power-bi-reports/

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[T0_0H]

Hi @v-jianpeng-msft ,

Thank you for taking the time to review my post and your sugested solution. 
I have taken your advice and tryed implemnting the above. Even when leveraging the username function in a meause and using that in the calculated coulmn in conjunction with the calculatetable and/or filter functions I am reciving errors. These all come back to Username() funtion not being compatable within calculated coulmns. It seems even using the measure CurrentUsers = Username() does not allow a work around.

Right now it's seeming that the request is not possible. If i find a solution that works down the line I will share it here. 

[123abc]

1. Requirement: Expose data from the 'Client Details - Privileged Information' table via calculated columns in the 'Client Details' table while maintaining security filtering based on the bridging table and row-level security (RLS).

2. Challenge: You're using a bridging table for security purposes, and RLS is applied based on the username. You want to ensure that users only see the data they are authorized to see.

3. Proposed Solution: Utilize calculated columns in the 'Client Details' table to fetch data from the 'Client Details - Privileged Information' table while respecting security filters.

Here are some considerations and potential solutions:

Solution 1: DAX Calculated Columns

While calculated columns are computed at the time of data refresh and are not affected by row-level security, you can still use DAX expressions to bring in related data. However, since calculated columns are static, they might not respect dynamic security filters set by RLS.

However, if your RLS logic is purely based on the username and company, you might be able to leverage calculated columns in combination with existing relationships and DAX expressions to achieve your goal.

Example DAX Expression for Calculated Column in 'Client Details':

Willingness to Engage =
CALCULATE(
MAX('Client Details - Privileged Information'[Willingness to Engage]),
FILTER(
'Bridge Client Details Privileged Information',
'Bridge Client Details Privileged Information'[CompanyID] = 'Client Details'[CompanyID] &&
'Bridge Client Details Privileged Information'[Staff Username] = USERNAME()
)
)

Solution 2: Virtual Relationships

Virtual relationships allow you to define relationships using DAX expressions instead of physical relationships in the model. You might explore this option to dynamically establish relationships between tables based on security context.

However, virtual relationships might not fully solve your problem, as they might still not respect RLS on the 'Client Details - Privileged Information' table.

Solution 3: Simplifying the Model

Consider simplifying your model by reevaluating the need for the bridging table and the complexity it introduces. Can you achieve the same security requirements using more straightforward methods, such as roles and permissions within your database or application layer?

Conclusion

While it's possible to use calculated columns and relationships to expose data from the 'Client Details - Privileged Information' table, the challenge lies in ensuring that the security filters are respected. You might need to combine various techniques and possibly revisit your data modeling and security architecture to find the most efficient and secure solution.

Experiment with the suggested solutions and consider consulting Microsoft documentation or seeking assistance from experienced BI developers for further guidance tailored to your specific requirements and environment.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[T0_0H]

Hi @123abc ,

Thank you for taking the time to review my problem and for provided a suit of possible solutions. 

Solution 1: DAX Calculated Columns

I tried to implement this but it doesn't seem viable for this situation. Similar to the issue with @v-jianpeng-msft  solution the UserName() function can not be leveraged within a calculated column. 

Solution 2: Virtual Relationships

I'm unsure how to go about designing a solution using the virtual relationship as again i would need it to be dynamic and I'm not sure if this would work within a calculated column. Have you encountered any blogs or documentation that springs to mind that could relate to this situation? I've have done a lot of searching but have not yet found anything that could support my use case.

Solution 3: Simplifying the Model

For our model we are leveraging Roles and RLS. In this case the request for the 'Client Details' table is to have no RLS on it as they wish that dataset to be visible to all users. It is only these specific additional columns that are to show different results depending on the user accessing it. 

Thank you again for your time & help. If i come across a solution that works for my use case I will share here so others can leverage it in future.