Measure Visibility when column is secured by OLS

Question

Hi,

We have a requirement where we want to hide the value in a column, based on their role, for some users in Analysis service TM. But let them see the value in the measure that is using this column as part of its formula. How can be achieve this?

If we secure that column by OLS then the measure is not working. If we remove OLS then the column value is visible.

We tried to hide the column and it is some what effective in Excel. But in PowerBI Desktop, there is a choice for users to see the hidden columns and measures which defeats the purpose of hiding them.

Thanks.

Answer 1

RLS is generally an on or off thing- either the use has access the the data or not. You might be able to build something into your RLS filters that could handle it, but I don't recommend that option if you can avoid it as it might cause some performance issues.

An aggregation table might be a good solution, depending on what you are trying to hide. For example, if you had a fact table that was sales amounts by month/state but want to limit your users to the specific state they are working in. role-level or object (column) level works fine for that. If you wanted to have overall totals without having access to the state level data, you could create a second sales table with month and sales in whatever aggregations you need- average, sums, etc. and let everyone have access to the aggregated data. Essentially the separate table would fill in that part of the measure, without making any of the specific details available to your users.

Please do let me know if this would be a workable solution for your scenario or if you have any further questions.