DAX Formula to restrict data

DAX Formula to restrict dataFollow
Ben Hopes
Ben HopesSep 06, 2022 06:48 AM
Please can someone help with a row level security query. I have the following tables Entity A …
1. DAX Formula to restrict data

Ben Hopes
Posted Sep 06, 2022 06:48 AM
Please can someone help with a row level security query.

I have the following tables

Entity A

User

PaulSmith@A.com

JohnSmith@A.com

CarlSmith@A.com

Entity B

Name

Icon

A

Z

B

Z

C

X

D

X

F

C

ZA

C

I need to create a DAX formula to allow row level security on Entity B for the following logic

If a user is not in Entity A table then the user should be restricted to see only records where Icon <> C. If the user is in Entity A then they will have access to all records in Entity B

Any help much appreciated

——————————
Ben Hopes
FY3 9HR
——————————

2. RE: DAX Formula to restrict data

Kaz Shakir
Posted Sep 09, 2022 07:08 PM
Edited by Kaz Shakir Sep 09, 2022 07:09 PM | view attached
@Ben Hopes,
I think this is relatively straight forward. I would create three measures:

1. First, a measure to determine the current user:
CurrentUser = USERNAME()​
​Note: there is also another function called USERPRINCIPALNAME() – you should try both to see which one gives you the result you need.

2. Next, I would create a measure to determine if the current user is in Entity A:
CurrentUserInEntityA =
VAR _entityAMatchedRows =
FILTER(
ALL(‘Entity A’),
‘Entity A'[User] = [CurrentUser]
)
RETURN
COUNTROWS(_entityAMatchedRows) > 0​
3. And third, I would create a measure to determine whether to show a given row in the current filter context:

DisplayRow =
VAR _currentIcon =
MIN(‘Entity B'[Icon])
RETURN
IF(
AND(
NOT([CurrentUserInEntityA]),
_currentIcon <> “C”
),
“Display”,
“Don’t Display”
)
And then you can create a simple table visual to show Entity B, and use the [DisplayRow] measure as a filter on that visual:

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

Attachment(s)

pbixHopesExample_v1.pbix