Override or Ignore Row Level Security for KPI Indicator

Maria Megnia 31 Reputation points
2022-07-14T17:22:41.803+00:00

We have a tabular field called [Gross Margin %] that we hide from end users using a security role in a tabular SSAS model

We have a KPI that color codes Red/Yellow/Green based upon [Gross Margin %]

We want the users to see the KPI colors, but not the related measure value at any level of drill through

Is there a way to allow users to see the KPI but not the base measure?

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,344 questions
0 comments No comments
{count} vote

Accepted answer
  1. Alexei Stoyanovsky 3,416 Reputation points
    2022-07-15T06:09:26.837+00:00

    RLS doesn't offer special treatment for KPIs: if a row is secured, it is secured for each and every purpose. While your mention of securing a column seems to be rather about OLS than RLS, OLS is just as indiscriminate. Your only chance is to have an unsecured column with just the color values.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Joyzhao-MSFT 15,636 Reputation points
    2022-07-15T02:31:39.217+00:00

    Hi @Maria Megnia ,
    In theory, you can apply security only to tables and columns, not to measures. Measures are hidden if they have a dependency on a column or table that is hidden because of a security role. However, there is a way to hide a measure without hiding any of the tables and columns of the model: just create a hidden empty table that is not really used in any calculation, but that is referenced in an unused variable of the measure you want to hide. Then, hide that table, and the measure will be hidden, too.

    You can also group measures from multiple tables into one table by creating an empty table, and then moving or create new measures into it.

    Keep in-mind, you may need to include table names in DAX formulas when referencing columns in other tables.
    Best Regards,
    Joy


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.