Hi @ribisht ,
Great questions! Let’s break down your concerns step by step.
1. Column-Level Security and Measures:
In Power BI, Row-Level Security (RLS) is applied to rows in a table based on user roles. Column-Level Security (CLS) isn’t directly supported as a built-in feature in Power BI; however, you can achieve column-level security by controlling access to columns using DAX expressions. This method applies to measures as well, but the important thing to note is:
When you create a measure using a restricted column, the measure will still follow the column security if you ensure the DAX expression considers the role-based access.
To ensure the measure respects column-level security, you need to apply the same security logic to the measure. Here’s an example of how you could apply this:
DAX
Sales Pareto Measure =
IF(
USERNAME() = "UserA",
BLANK(),
SUM(FactTable[SalesParetoAmount])
)
In the above DAX code:
The IF condition checks if the logged-in user is "UserA." If true, the measure returns BLANK(), effectively restricting UserA from viewing the Sales Pareto amount.
For other users, it will return the correct value.
This ensures that when User A tries to view the report, the measure based on Sales Pareto won’t display any data.
2. Columns from Tables Without Access:
For your second use case, where a user doesn't have access to a specific table (e.g., Table B), the behavior you’re encountering is because Power BI applies security at the data model level. If a user doesn't have access to a table or column, they cannot see it in any visual, including measures derived from those columns, which results in an error message.
To resolve the issue where the visual fails but you want the columns to show NULL or BLANK, you can do the following:
Use DAX measures to check the user's access to the table and return a blank or null if they don't have access.
You can create a "safe" measure that returns a blank for restricted users, as shown below:
DAX
SalesAmountWithAccess =
IF(
ISINSCOPE(TableB[Column]),
SUM(TableB[SalesAmount]),
BLANK()
)
In this case:
The ISINSCOPE function checks if the column from Table B is in the current context. If the user does not have access to Table B, the measure will return BLANK().
This ensures that for users without access to Table B, the columns from that table will be displayed as NULL or BLANK, while still allowing other columns and measures to work normally.
Measure Level Security: Ensure you add logic to your DAX measures that takes the user’s role or access rights into account, and returns BLANK() or a restricted value if necessary.
Column Access and Visibility: When users don't have access to specific tables, you can use DAX expressions to control the visibility of columns and prevent errors by showing BLANK() for restricted data.
By implementing these solutions, you can ensure that the visuals behave as intended and that users see only the data they are allowed to access.
Please mark this post as solution if it helps you. Appreciate Kudos.
