Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Pushpak
Frequent Visitor

Configuring “all” access with dynamic row-level security

Hi,

 

Pushpak_0-1644399135545.png 

Pushpak_0-1644476805280.png

 

 

Pushpak_4-1644399867765.png

 

These are two tables i have used for Dynamic RLS and above DAX i have used for accessing the region but not working for region = "All". Please can anyone help me with DAX expression that will be helpful.

 

Thanks.

 

 

 

 

 

 

1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

Hi @Pushpak ,

 

Based on your description, please create a column to show the CEO of these Managers.

CEO = IF('Table'[Region]="ALL",BLANK(),CALCULATE(MIN('Table'[ID]),FILTER(ALLEXCEPT('Table','Table'[Country]),'Table'[Region]="ALL")))

Then use PATH function to create a new column to show the whole path for the hierarchy:

PATH = PATH('Table'[ID],'Table'[CEO])

Finally you can use PathContains functions to check if ID exists in a path or not,then you need to add this logic as a role in the table.

PATHCONTAINS('Table'[PATH],
MaxX(
Filter(
'Table',
[Users]=USERPRINCIPALNAME()
)
,'Table'[ID]
)
)

Here are some helpful blogs for your reference:

https://radacad.com/dynamic-row-level-security-in-power-bi-with-organizational-hierarchy-and-multipl... 

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi 

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Spinhalyard
Regular Visitor

Choose a field that will never be empty and do this for the superuser role...

[fieldname] <> ""

V-lianl-msft
Community Support
Community Support

Hi @Pushpak ,

 

Based on your description, please create a column to show the CEO of these Managers.

CEO = IF('Table'[Region]="ALL",BLANK(),CALCULATE(MIN('Table'[ID]),FILTER(ALLEXCEPT('Table','Table'[Country]),'Table'[Region]="ALL")))

Then use PATH function to create a new column to show the whole path for the hierarchy:

PATH = PATH('Table'[ID],'Table'[CEO])

Finally you can use PathContains functions to check if ID exists in a path or not,then you need to add this logic as a role in the table.

PATHCONTAINS('Table'[PATH],
MaxX(
Filter(
'Table',
[Users]=USERPRINCIPALNAME()
)
,'Table'[ID]
)
)

Here are some helpful blogs for your reference:

https://radacad.com/dynamic-row-level-security-in-power-bi-with-organizational-hierarchy-and-multipl... 

https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi 

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@V-lianl-msft 

it worked.

Thanks

amitchandak
Super User
Super User

@Pushpak , This is what I have. I created

A table for the region

two columns

Region , region with all

northwest , northwest 

northwest, All

 

Mean all one entry for the region with the region  and one entry of region with all

 

merged two tables in power query using region in security table and region with all in this new table

 

you will get a new column with all region name

 

that you can join with dimension and you can simply filter userprincipalname

@amitchandak 

Can't we write DAX for this scenario ?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.