This is an awesome addition! I have been looking for a solution to this use case in conjunction with RLS for a long time. We have an application that generates a 1:M access for RLS and works great. However, there is a cost associated with the user licensing for the app. The executives responsible for the respective portfolios never use the app itself but rather the output and analytics from that app. In the past we needed to provision a license to the app in order to provide the required access. This solves that problem! To your point, there is probably a less static and more sustainable solution to, for lack of a better term, hard-code it in the service but this opens up a tremendous opportunity for me. Thank you! Made my day.
For larger groups where you won't have auto access to email (or email won't match user principal name) you can create a spreadsheet that contains what they are allowed to see. The country would be a single field and you'd have to list each country separated by something, I use semicolon ";". In power Query, split the country column into rows and create a many to many relationship from your excel table to your sales table. Then in the Dax, the security says you can only see rows of the new table based on user principal name. It is still manual compared to other the other solution, but you can keep it in a secure folder location and updating security is as easy as updating a line in excel. The draw back is the user has to wait until the next refresh cycle for the changes to take place.
Great video. Apologies for unrelated: Video request for your users: Field Parameters. Use case: Set up a field parameter with multiple measures, with an additional field grouping measures together. Use this on a matrix visual with an associated filter with these fields to allow users to choose which columns to include; this allows a ton of measures to be available to the user, with the benefit of only having the ones they want (as well as better performance). In addition, you can use field parameters to allow the user to include/exclude different drill down options (with bookmarks allowing multiple field parameter groups to change the order of the drill downs). My experience comes from business users wanting Excel like experience in the form of Matrixes. Utilizing field parameters in this way has been very beneficial to their experience. I'm happy to expand on this comment, just let me know.
I have a need to make dynamic RLS based upon Azure AD group memberships. But to do that I need to have a dataflow that calls the AD Graph API, bringing the various groups im using and their associated members, and to do that I need to have an app registration between Power BI and Azure AD to use Graph API data as a data source. It turns something seemingly straight forward into something overly complicated. 😢
I haven't tried, but Power Bi has a new data connector for Azure Active Directory. May be if you could check for that. But I suppose one still have to open Powershell and install the required before connecting with the new connector.
I'm not sure if this is dependent on Premium, but in our environment we have our PBI workspaces in the same domain as our Azure AD. For reports that have RLS, I can go into Security and on the permissions menu where Patrick shows adding a user, I can enter an AD Group name instead. This effectively sets all AD group members as having that RLS Role.
@tigerthecat006 that would work but my problem is that the number of combinations of RLS groups number in the 100s. I need a way to connect the list of RLS groups to their associated security key column, and then bring in the list of the users in those groups for the UPN function to map onto.
How would you go about testing Dynamic RLS for multiple reports that share a dataset? The PBI Service GUI no longer allows you to "Test as..." for each report that is connected to a data model now.
@guyinacube This would be an interesting question for me too. After the new look ( which you cant diable anymore ) its impossible to reports as another user that share a dataset. Common usecase for us.
One thing I need to find in RLS is dynamic 2 roles, I want someone to see their stats and 1 tier up IE rest of the team. I suspect I can add the users email and add managers email in the users table, setup RLS and users could switch roles somehow
If i was another user (MrSmith) and connected to the report, would I see both International and Domestic? Is the default to block all others? AND - Thanks for sharing 💥💥💥💥
@guyinacube after you publish to the service and you are in the security section for that dataset and you've already added the user to the RLS role, is there a reason you chose to switch to a different user to show they had the correct access instead of using the test role option that comes up when you click the three dots That appear when you hover over the role name i.e Domestic
Hi there, Do you have a hint how to overcome RLS topology restrictions of limited relationships? Having a dim table (RLS applied on it) in the remote model that filters a fact table in the local model: RLS is applied as expected, except for totals are visible, because there are not filters applied there. I was thinking about rewriting the measures adding a constant filter from that table with CALCULATE, but probably there are better approaches :)
Patrick you have not yet helped me in one of the matrix issue. How should I control the total and subtotal values for a hierarchy? I have posted it in community but it seems no one is that smart enough to crack it down.
Hey guys! Love this... Ive been trying to get round RLS in my organisation, but keep stumbling with my project. I wonder if you could help. Scenario: im building a Performance Mi dashboard for my organisation, but our data policy doesn't allow us to store personalised information on powerBi and publish it to the Service. Therefore, I'm not allowed to store email address details in the dashboard. The dashboard works off of a direct query to a SQL database to extract the performance data. Are you aware of a way for a published PowerBi dashboard to identify the user, alter the Direct Query and only extract that individuals data from the SQL Database? Love the content, you guys are Heros!
Hey Thomas, could you be a little more specific? You are not allowed to store personal information on Power Bi plus you are using direct query right? In direct query power bi is not storing the data. But could you please tell me if your database has any username aur email ID details?
I want to add 100 RLS to Power BI, is there any quick method to do that, or I just need to type in one by one? Can we post this idea to MS Power BI Team, to bulk upload RLS from excel to Power BI?
Will this remove the old dax rls? I currently have one master role which feeds into an excel sheet. The sheet then lists the users and all their different access rights. This made it easier to maintain as have over 100 different views and only need to update an excel row and not create a new role
Q: I tried dynamic rls using username and principalname however microsoft says using their power bi server automatically makes it look for email. However in my company its set up as domain\username without email address. How do I create the proper rls with username for power bi server? tx ps i am using power bi may 2023
Also is anyone else finding the new preview RLS editor (shown in the vid) as super slow to load your existing roles when you open it? Unusable nearly...
Why always changing to more simple stuff which make sense but with putting "old" users to possible misleading approach. I would feel better with the option to DAX activated by default. Anyway it is still a good move for non DAX users.
![[UA] Team Vitality проти NAVI | IEM Cologne 2024](http://i.ytimg.com/vi/wttO1zVRTC0/mqdefault.jpg)
This is an awesome addition! I have been looking for a solution to this use case in conjunction with RLS for a long time. We have an application that generates a 1:M access for RLS and works great. However, there is a cost associated with the user licensing for the app. The executives responsible for the respective portfolios never use the app itself but rather the output and analytics from that app. In the past we needed to provision a license to the app in order to provide the required access. This solves that problem! To your point, there is probably a less static and more sustainable solution to, for lack of a better term, hard-code it in the service but this opens up a tremendous opportunity for me. Thank you! Made my day.
Great example and explanation ... this was exactly what I was looking for. THANK YOU!
For larger groups where you won't have auto access to email (or email won't match user principal name) you can create a spreadsheet that contains what they are allowed to see. The country would be a single field and you'd have to list each country separated by something, I use semicolon ";". In power Query, split the country column into rows and create a many to many relationship from your excel table to your sales table. Then in the Dax, the security says you can only see rows of the new table based on user principal name. It is still manual compared to other the other solution, but you can keep it in a secure folder location and updating security is as easy as updating a line in excel. The draw back is the user has to wait until the next refresh cycle for the changes to take place.
If you have to many countries to list, then perhaps use region instead. If region doesn't exist, then creating one with Dax should be relatively easy.
Awesome. I use RLS so sales people only see their commission sales and not everyone else’s.
Any tips to embedding Power BI Report to SharePoint site with RLS?
Great video.
Apologies for unrelated:
Video request for your users: Field Parameters.
Use case:
Set up a field parameter with multiple measures, with an additional field grouping measures together. Use this on a matrix visual with an associated filter with these fields to allow users to choose which columns to include; this allows a ton of measures to be available to the user, with the benefit of only having the ones they want (as well as better performance). In addition, you can use field parameters to allow the user to include/exclude different drill down options (with bookmarks allowing multiple field parameter groups to change the order of the drill downs).
My experience comes from business users wanting Excel like experience in the form of Matrixes. Utilizing field parameters in this way has been very beneficial to their experience.
I'm happy to expand on this comment, just let me know.
Wonderful option
Thanks. Great video !
Great video Patrick !
I have a need to make dynamic RLS based upon Azure AD group memberships. But to do that I need to have a dataflow that calls the AD Graph API, bringing the various groups im using and their associated members, and to do that I need to have an app registration between Power BI and Azure AD to use Graph API data as a data source. It turns something seemingly straight forward into something overly complicated. 😢
I haven't tried, but Power Bi has a new data connector for Azure Active Directory. May be if you could check for that. But I suppose one still have to open Powershell and install the required before connecting with the new connector.
I'm not sure if this is dependent on Premium, but in our environment we have our PBI workspaces in the same domain as our Azure AD. For reports that have RLS, I can go into Security and on the permissions menu where Patrick shows adding a user, I can enter an AD Group name instead. This effectively sets all AD group members as having that RLS Role.
@tigerthecat006 that would work but my problem is that the number of combinations of RLS groups number in the 100s. I need a way to connect the list of RLS groups to their associated security key column, and then bring in the list of the users in those groups for the UPN function to map onto.
You are the best❤❤❤
great video, short and to the point
I need to use RLS, but I want to show max, min and median without RLS. Can I turn off RLS for some measures? Thank you
That's a pretty good question. I would try this one if it's possible or not and then will let you know.
How would you go about testing Dynamic RLS for multiple reports that share a dataset? The PBI Service GUI no longer allows you to "Test as..." for each report that is connected to a data model now.
@guyinacube This would be an interesting question for me too. After the new look ( which you cant diable anymore ) its impossible to reports as another user that share a dataset. Common usecase for us.
One thing I need to find in RLS is dynamic 2 roles, I want someone to see their stats and 1 tier up IE rest of the team. I suspect I can add the users email and add managers email in the users table, setup RLS and users could switch roles somehow
Gui is for basic or for simple rules, MS PBI team, this feature is like more dev time and less impact for users.
If i was another user (MrSmith) and connected to the report, would I see both International and Domestic? Is the default to block all others? AND - Thanks for sharing 💥💥💥💥
It will block everything else.. only people added to the RLS can see the data
This is awesome {"loop"}!!
Fortunately my row level security doesn't prevent me from watching Patrick's videos! #iwatchpatrick
@guyinacube after you publish to the service and you are in the security section for that dataset and you've already added the user to the RLS role, is there a reason you chose to switch to a different user to show they had the correct access instead of using the test role option that comes up when you click the three dots That appear when you hover over the role name i.e Domestic
@GuyInACube Do you have any idea how to merge a live connected Power BI report to a Power BI dataset into one pbix file in import mode?
That's a mixed mode. I think you can give it a try if you are trying to merge in desktop.
Hi there,
Do you have a hint how to overcome RLS topology restrictions of limited relationships?
Having a dim table (RLS applied on it) in the remote model that filters a fact table in the local model: RLS is applied as expected, except for totals are visible, because there are not filters applied there. I was thinking about rewriting the measures adding a constant filter from that table with CALCULATE, but probably there are better approaches :)
Patrick you have not yet helped me in one of the matrix issue. How should I control the total and subtotal values for a hierarchy? I have posted it in community but it seems no one is that smart enough to crack it down.
This is awesome! What if I wanna see the data of "United States" in International as well?
Write code for "AND" or "OR" in the Dax code
Hey guys! Love this...
Ive been trying to get round RLS in my organisation, but keep stumbling with my project. I wonder if you could help.
Scenario: im building a Performance Mi dashboard for my organisation, but our data policy doesn't allow us to store personalised information on powerBi and publish it to the Service. Therefore, I'm not allowed to store email address details in the dashboard.
The dashboard works off of a direct query to a SQL database to extract the performance data.
Are you aware of a way for a published PowerBi dashboard to identify the user, alter the Direct Query and only extract that individuals data from the SQL Database?
Love the content, you guys are Heros!
Hey Thomas, could you be a little more specific? You are not allowed to store personal information on Power Bi plus you are using direct query right? In direct query power bi is not storing the data. But could you please tell me if your database has any username aur email ID details?
I want to add 100 RLS to Power BI, is there any quick method to do that, or I just need to type in one by one? Can we post this idea to MS Power BI Team, to bulk upload RLS from excel to Power BI?
@guyinacube
I would still prefer Dax version though.
I still can't see this in power bi desktop... How to enable it... Can anyone help me?
Will this remove the old dax rls? I currently have one master role which feeds into an excel sheet. The sheet then lists the users and all their different access rights. This made it easier to maintain as have over 100 different views and only need to update an excel row and not create a new role
Hi Patrick
@guyinacube I wonder if we can use report parameters as the value in the dax expression
Which report parameters you are talking about? Generate series one?
@@beyondtruthandlies sorry I meant dataset parameters (transform data > manage parameters)
@@BootleggedBatman Report parameters can only be used in the M Query part. I too wish we could have used it in the DAX.
Q: I tried dynamic rls using username and principalname however microsoft says using their power bi server automatically makes it look for email. However in my company its set up as domain\username without email address. How do I create the proper rls with username for power bi server? tx ps i am using power bi may 2023
I feel like this isn't that new? or are we strictly talking about the switching between dax editor
What version is that? I just installed 2.115.663.0 and I'm not seeing the same menu items in Manage Roles.
Same question here!
You may need to enable the feature in the preview from options panel
@@dezcraft_dev Much appreciated, I'll give that a try.
I wish MS would just let us bulk import RLS... who cares about a different GUI for entering the data, just let us import it as some sort of flatfile!
Also is anyone else finding the new preview RLS editor (shown in the vid) as super slow to load your existing roles when you open it? Unusable nearly...
Why always changing to more simple stuff which make sense but with putting "old" users to possible misleading approach.
I would feel better with the option to DAX activated by default. Anyway it is still a good move for non DAX users.