Managing hierarchical organizations in Power BI security roles

No video

Managing hierarchical organizations in Power BI security roles

Рет қаралды 19,321

Күн бұрын

How to apply dynamic security roles in a hierarchical organization to minimize the maintenance effort on the security configuration and obtain the best performance at query time.
Article and download: sql.bi/805866?...
How to learn DAX: www.sqlbi.com/...
The definitive guide to DAX: www.sqlbi.com/...

Пікірлер: 23

@timolff9239 Жыл бұрын

Thanks for reading my mind. I was just working on this today and then you release this video. Keep that third eye open!

@tamerjuma Жыл бұрын

Thank you Marco. This is extremely useful. Appreciate your efforts sharing you magnificent knowledge!

@gordsimpson8916 Жыл бұрын

Thanks, Marco, I'm working on setting up a properly laid-out hierarchical table. I've been using PowerQuery to build the RLS table, which generates a large RLS table. Looking at doing it play around with your method

@Bharath_PBI Жыл бұрын

Very nice and useful 👍 Thank you!!

@martinbubenheimer6289 2 ай бұрын

This really simplifies security roles! Would the performance be any different when transferring the selection of PermissionsExtended to Organization using TREATAS on the same relationship column in an RLS formula on Organization rather than the many-to-many relationship?

@SQLBI 2 ай бұрын

The relationship could give a performance advantage.

@JuanOlCr Жыл бұрын

Thank you Marco. Yet again another great and helpful video. I wonder if you could make a video on how to implement RLS for benchmarks where the logged user has access to his own data, but other entities should me masked while being able to compare against them. Thank you.

@SQLBI Жыл бұрын

Check out this article: www.sqlbi.com/articles/implement-non-visual-totals-with-power-bi-security-roles/

@JuanOlCr Жыл бұрын

@@SQLBI Much appreciated

@lovlyhearts288 11 ай бұрын

I am having one doubt but not related to this content, Using teatedas Dax I connected date table and fact table. When user want to drill fact table aggregation value it's not filter date table untill I drag & make active relationship in the model, Is any other alternative way there to achieve this ?

@pradumn6914 11 ай бұрын

Hi sir I am using the same hierarchy method but at a region level. For example, my data has 5 levels of hierarchy (Continent-->Country--->State--->City--->District) and each user has permission for different hierarchy. I have used the same methodology and was able to reach the desired output table. But when I am trying to build the relationship, it shows a circular dependancy error. Any idea what am I doing wrong ?

@SQLBI 11 ай бұрын

Check the functions used in calc columns and calc tables: www.sqlbi.com/articles/understanding-circular-dependencies

@pradumn6914 11 ай бұрын

@@SQLBI Tysm sir for the suggestion. Worked like a charm. Used FILTER function instead of REMOVEFILTERS. Thanks again for this wonderful video :)

@rrr9110 4 ай бұрын

how do we manage this for dynamically, i mean maintaining this in a sharepoint spreadsheet and allowing the user to dynamically changing the access

@SQLBI 4 ай бұрын

You should import the configuration table from SharePoint (e.g. an Excel file containing the description of the hierarchy).

@annakasyna8576 11 ай бұрын

thank you Marco, Any idea why I am getting circular dependency error when trying to set up the relationship between the 'ManagerID' columns? What did I miss?

@SQLBI 11 ай бұрын

Check DAX functions used in calculated table and columns: www.sqlbi.com/articles/understanding-circular-dependencies

@giodanigr 4 ай бұрын

I got the same error, I fixed it by making a duplicate organization table, than make the permissionexpanded table with this duplicate organization table. Than make a connection between the original organization table and the permission expanded table. Last but not least, I change the manager id name in the permission expanded to the original organization manager id and remove the duplicate organization table.

@karthikkumar3854 7 ай бұрын

@marco, What's the logic behind building Permissions table?

@SQLBI 7 ай бұрын

It depends on requirements - try to write in the comments to the article (follow the link in the description) there are already several comments there you may want to review.