Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to Solution.
The solution is :
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.
The solution is :
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.
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
115 | |
109 | |
95 | |
58 |
User | Count |
---|---|
174 | |
148 | |
134 | |
105 | |
82 |