Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have dynamic row level security set up in my report that works in a sort of hierarchical sense where you are given access at one of three levels. I have included the DAX for each level below:
Top Level:
VAR _t =
CALCULATETABLE (
VALUES ( 'Access_Table'[Access_Required] ),
FILTER ( 'Access_Table', 'Access_Table'[User_Email] = USERPRINCIPALNAME () )
)
RETURN
IF (
CONTAINS ( _t, 'Access_Table'[Access_Required], "National" )
|| CONTAINS ( _t, 'Access_Table'[Access_Required], "Regional" )
|| CONTAINS ( _t, 'Access_Table'[Access_Required], "Individual" ),
'TableA'[Company]
IN CALCULATETABLE (
VALUES ( 'Access_Table'[Compan] ),
FILTER ( 'Access_Table', 'Access_Table'[User_Email] = USERPRINCIPALNAME () )
),
TRUE()
)
Middle Level:
IF(
CONTAINS(
CALCULATETABLE(
VALUES( 'Access_Table'[Access_Required] ),
FILTER( 'Access_Table', 'Access_Table'[User_Email] = USERPRINCIPALNAME() )
),
'Access_Table'[Access_Required], "Regional"
),
'TableB'[Region]
IN CALCULATETABLE (
VALUES( 'Access_Table'[Region]),
FILTER('Access_Table','Access_Table'[User_Email] = USERPRINCIPALNAME() )
),
TRUE()
)
Bottom Level:
IF(
CONTAINS(
CALCULATETABLE(
VALUES( 'Access_Table'[Access_Required] ),
FILTER( 'Access_Table', 'Access_Table'[User_Email] = USERPRINCIPALNAME() )
),
'Access_Table'[Access_Required], "Individual"
),
'TableC'[AGENCY]
IN CALCULATETABLE (
VALUES( 'Access_Table'[Agent_ID]),
FILTER('Access_Table','Access_Table'[User_Email] = USERPRINCIPALNAME() )
),
TRUE()
)
All three queries refer to an access table which basically consists of the relevant information needed to filter the report. The problem I have is that when a user is not contained in the access table they are able to view all data in the report.
Is there an error in the DAX that is allowing this to happen?
Solved! Go to Solution.
Hi @Anonymous ,
_t will return blank value when a user is not contained in the access table. It will always return TRUE in your original IF() formula.
So you need to replace TRUE() with FALSE() or BLANK().
Hi @Anonymous ,
_t will return blank value when a user is not contained in the access table. It will always return TRUE in your original IF() formula.
So you need to replace TRUE() with FALSE() or BLANK().
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |