Apply RLS to a calculated table

[AmiraBedh]

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. 

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.

[AmiraBedh · Accepted solution]

The solution is :

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.

[v-junyant-msft]

Hi @AmiraBedh ,

I saw that you posted the same question here and marked it as answered. Is your problem solved? If you have solved it, please mark this post as Answer so that more people who face the same problem and want to learn can find the solution. Thank you!
powerbi - Apply RLS to a calculated table - Stack Overflow

Best Regards,
Dino Tao