Dynamic RLS with DAX Filters

[Manny358]

I need to apply a DAX filter that will filter with column value in the Security Table into the Fact TAble. Each user's access is defined according to the Sales data, based upon if they have the right values of dimensions specified in the Security Table Row:

Security Table: 

With the proper Dimension Key aggregate (BUKey | CountryKey | CategoryKey | ProductKey), user can access RLS data row in Sales, if the Security Table 'keys' map to the Sales Table values for BU, BUSegment, Country, Category and Product.  The row should only for direct RLS permissions:

Data Model is like this with the need to decouple Security table from Data Model.

I have defined a Role for RLS as follows with following DAX Filter .. 

In text, the DAX Filter is: 

VAR vList = CALCULATETABLE(
VALUES( 'Security'[BUKey] ),
'Security'[Email] = USERPRINCIPALNAME()
)
RETURN
IF(
CALCULATE( COUNTROWS( RELATEDTABLE('Sales') ), Sales[BU] IN vList ) > 0,
TRUE(),
IF(NOT ISEMPTY(vList) && ISNONTEXT(FIRSTNONBLANK(vList, true)), TRUE(), FALSE())
)

I've mocked this up in a simple PowerBI so that user.one, user.two can only see the data that maps to their entry in the Security Table. Then doing a view role as:

I do not see the correct data in the output Sales table for this user.one (or other users). I'm getting empty results back. I've had this working partially with some mixed results (mixed output from multipe users - user.one, user.two) but need to understand how the DAX Filter should be. Many thanks in advance. 

sample data model is here:

powerbi,pbix 

[Manny358]

Any DAX gurus? I have a feeling this will need a filter across the transaction (Sales) table using the Summary featuer in CalculateTable. 

[v-yalanwu-msft]

Hi, @Manny358 ;

First of all, I have checked your data. [BUKey] in the Security table contains a space in the first place, while [BU] in other tables has no space, so the value cannot be matched.

So, first you need to change the BUKey data in the Security table to make it consistent.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Manny358]

Thanks for noticing that error in the column! I had actually turned off the filter on that dim and continued testing with the Filters. This partially helps .. now, I can demonstrate the real problem.

What you want is to map from the Security table, the dimension values BusUnitKey|BUSegmentKey|CountryKey|CategoryKey|ProductKey to the same value set of keys as defined in the Sales table. When the values match exactly, that user then has access to that row in the Sales table. It is nearly working as you can see below from the report produced ..

However, the exception (highlighted in green), is that when the security table row does not have a corresponding row entry match into Sales, then the next closest match is picked up instead of returning no entry. So in the example Sales table, the row in green should not be displayed, as CountryKey=Belgium and there is no corresponding entry in Sales table for Belguim with those other column values. 

Sales table (row 6 should not be accessed) 

Filter across all dim tables is similar as below ..

The filter needs to be more like .. 

So it doesn't slice the dim tables individually with valid key values but takes the whole value set as one composite. Not sure if this will work across the dim tables. Dont' want to necessarily do this in the Sales  table as performance could be an issue with millions of rows. Any help appreciated. 

[Manny358]

power-bi dataset 

sample data attached

[Manny358]

Any DAX experts out there? 🙂 I'm still new to this. 

I'm wondering if COUNTROWS is proper way to aggregate all accessible RLS roles in the Sales table. 

Simply all the columns in Security Table that are defined for email user that match corresponding column values in the same row in Sales table should define access for each user. Each user should only be able to see their own roles.