Forum Discussion

NielsBakker's avatar
NielsBakker
Frequent Visitor
1 year ago
Solved

Dynamic RLS - DAX help needed

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, whi...
"DAX " "DAX help" 'Need help'
Dynamic RLS
  • v-venuppu's avatar
    v-venuppu
    1 year ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
1 year ago

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 )