powerbidaxmeasurerow-level-securitycalculated-tables

Apply RLS to a calculated table


I have the following calculated table :

MyCalculatedTable = 
DISTINCT (
    SELECTCOLUMNS (
        FILTER (
            'Dim A',
            LEFT('Dim A'[Group],1) = "B" &&
            LEFT('Dim A'[Id],1) <> "C" &&
            'Dim A'[Code Type] IN {"A", "Y"}
        ),
        "Group", 'Dim A'[Group]
    )
)

I am applying RLS on the Dim A and of course other tables linked to Dim A  like fact tables so in this case the RLS won't be applied since the table is generated during data load /refresh, not dynamically during query time. Therefore, the calculated table itself does not automatically inherit RLS from the underlying tables unless the calculated table is directly filtered by those tables during the report viewing.

So how should I proceed in this way ?

What I thought about is to create a 1 to many relationship between the MyCalculatedTable calculated table and the Dim A based on the Group column and make the Cross-filter direction as Both and check the Apply security filter in both directions.

enter image description here

I have a measure that it is based on the calculated table :

Amount Paid = 
CALCULATE (
    SUM('Fact A'[Amount Paid]),
    FILTER (
        'Fact A',
        RELATED('Dim B'[Invoice Number]) IN VALUES('MyCalculatedTable'[Group])
    )
)

I have a guess that RLS will automatically be applied when the measure is evaluated in a report. This is because measures are calculated in real-time based on the data visible to the user, and RLS is enforced at this point.


Solution

  • You are correct in your thought process.

    1. Create the 1 to many relationship between the MyCalculatedTable table and the Dim A table on the Group column.
    2. Set the cross-filter direction as Both and check the Apply security filter in both directions.

    When RLS is applied to the Dim A table, the MyCalculatedTable table will be filtered, thus the VALUES('MyCalculatedTable'[Group]) in the measure will be filtered as well. This will result in a filtered SUM('Fact A'[Amount Paid]) value.