Dynamic RLS - DAX help needed

[NielsBakker]

Hi! 

In PowerBI, we have a data model and want to apply a quite flexible data security that includes all possible scenario's. To achieve this we are thinking about using a UserSecurity table, which has all the permissions per user. See below a short example of a scenario: 

In this case, user 16 can have access to subscription 25 but only to ResourceGroup 15348 within subscription 25. At the same time, user 16 can see everything in Subscription 17. We want to use null (wildcard) because otherwise the UserSecurityTable can get too large.

I tried to use relationships to the dimension tables, but in the above mentioned scenario, the null value overrides it.

Therefore, our idea now is to filter the fact table instead since all the id's are already in there (probably will get performance issues, but will solve that later). Further there won't be a relationship between the tables since we have to create multiple relationships between the 2 tables. So, we need a DAX query to solve this in Manage Roles  but my DAX is so far not good enough to make it work. 

In SQL, we would use the following query to achieve this: 

from FactTable1 fact
join UserSecurity usc on 
(
    (fact.subscriptionid = usc.subscriptionid or usc.subscriptionid is null) and
    (fact.resourcegroupid = usc.resourcegroupid or usc.resourcegroupid is null)
)
where usc.userid = 16

Could someone please help me with translating this SQL query to DAX? 

And I'm happy to receive some other idea's on how to set this data security up!

Many thanks!

Kind regards,

Niels

[v-venuppu · Accepted solution]

Hi @NielsBakker ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Greg_Deckler for the prompt response.

I tested it with "View as" and it worked as expected. The RLS filtered the RESOURCEUSAGECOST table correctly based on the UserSecurity table.
I manually dragged fields into the table visual, and only the allowed SUBSCRIPTIONIDs for the test user were shown.

Please find the attached PBIX file for your reference.

Thank you.

[NielsBakker]

@Greg_Deckler thank you very much for the quick response. Unfortunately, I get the following error message when applying the RLS. I've used your DAX on the Fact table that needs to get filtered in Manage Roles.

Not sure what needs to be changed to make it working. 

Let me know if more information is needed!

Many thanks!

Niels

[Greg_Deckler]

@NielsBakker Seems like maybe:

Measure =
  VAR __UserID = 16
  VAR __RG = DISTINCT( SELECTCOLUMNS( FILTER( 'usc', [userid] = __UserID ), "rg", [resourcegroupid] ) )
  VAR __Table = 
    FILTER( 
      'fact', 
      ( [subscriptionid] = __UserID || [subscriptionid] = BLANK() ) &&
      ( [resourcegroupid] IN __RG || [resourcegroupid] = BLANK() )
    )
RETURN
  COUNTROWS( __Table )