cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors