Forum Discussion

abhiram342's avatar
abhiram342
Microsoft Employee
3 years ago

Help on RLS in Power BI

Hi Team - We have tabular model (screenshot below) and enabled RLS on the "DimSubsidiary" table, which is applying security filter to the "FactSales" tables.

However, we have a requirement to show sales different when slicer is placed on Geography.

Example:

As per Sample Data below, User A is allowed to see US and UK Subsidiary Data

For User A: 

Total Sales  (Subsidiary=US) = 100 + 600 ( Row 1 + Row 2) . This is working as expected.

Total Sales (For Geo=US) = 100 ( because of RLS user A can see Row 1 and Row 2) and For US, it's 100.

but User A has access to US subsidary , they should be able to see Revenue for US Geography i.e they should see sales  as 

( 100 (Row1) + 500 (Row 3) + 700 (Row 5) Beacuse of RLS in place this will not work

Can you please suggest how to proceed further in order to show 1300 as sales for User A when we select Geo="US" ? 

Do I need to create any bridge table based of Fact? 

 

Data Model

 

Thanks,

Abhiram

 

 

"Need Help"
Dynamic RLS
Dynamic row level security
RLS problems

2 Replies

  • Mrxiang's avatar
    Mrxiang
    Helper II
    3 years ago
    Based on your requirement, it seems like you need to create a bridge table to connect the "DimSubsidiary" table and the "DimGeography" table. This bridge table will have a row for each combination of subsidiary and geography that a user has access to.

For example, if User A has access to US and UK subsidiaries and US and Canada geographies, the bridge table would have the following rows:

Subsidiary	Geography
US	US
UK	US
US	Canada
Then, you can create a measure in the "FactSales" table that sums up the sales for each combination of subsidiary and geography in the bridge table. The measure formula would look something like this:

Total Sales = 
CALCULATE(
    SUM(FactSales[Sales]),
    USERELATIONSHIP(BridgeTable[Subsidiary], DimSubsidiary[Subsidiary]),
    USERELATIONSHIP(BridgeTable[Geography], DimGeography[Geography])
)
This measure will take into account the RLS filter on the "DimSubsidiary" table and only show sales for the subsidiaries that the user has access to. It will also show sales for the geographies that the user has access to, based on the bridge table.

When the user selects "US" in the geography slicer, the measure will show the total sales for all subsidiaries that the user has access to in the US geography.

I hope this helps! Let me know if you have any further questions.
    • abhiram342's avatar
      abhiram342
      Microsoft Employee
      3 years ago

      Hi Mrxiang  - thank you for your respone. I have implemented your suggestions but it's not working i.e For User A TotalSales For US coming as 600 (TotalSales Measure) due to RLS but it should show 1300. Please let me know if I'm missing something.

       

      Measure:

      Total Sales =
      CALCULATE(
          SUM('Fact Sales'[SalesAmount]),
          USERELATIONSHIP('BridgeSubGeo'[SubsidiaryID], DimSubsidiary[SubsidiaryID]),
          USERELATIONSHIP(BridgeSubGeo[GeoID], DimGeography[GeoID])
      )
      RLS Code ON DimSubsidiaryTable:
      VAR SUBSIDIARIES =
      CALCULATETABLE (
      VALUES ( 'Dim User Security'[SubsidiaryID] ),
      'Dim User Security'[UserAlias] = USERPRINCIPALNAME ()
      )
      RETURN
      'DimSubsidiary'[SubsidiaryID] IN SUBSIDIARIES
      ModeL after adding bridge Table:

      Acutal Data:

      After RLS: