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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.