Forum Discussion
Manage Roles and View Roles from Excel Spreadsheet
Anonymous
Don't give up too soon on this. I promise - you can get this to work.
Also remember that you have imported the data into Power BI - so the fact that it's from Excel no longer matters - as it now resides in your Power BI file.
I'll demo with a simple example:
Let's say I have this table imported in Power BI Desktop
And I want your coworker to only see the data for Florida - you could create a role specific for only Florida like this
When I import this to Power BI service - anyone I drop into this Role - will only be able to see Florida.
Importantly though - Row Level Security ONLY applies to users in a "Viewer" role of dataset workspace. - So if you invited your coworker to your workspace as a "Member, Contributor, or Admin" - he/she will not be bound by RLS.
If you still run into issues - import a few screenshots that I can reference. I'll be able to better guide what needs to be fixed.
swise001 - thanks for posting this. This was helpful for sure! However, I fiddled with it some more, and found out it was based on the fact that the person was in my workspace, so even when I had her only in an exclusive role, her status as being in my workspace overruled this and that's why the filters weren't working! Such a simple solution....Thank you for your perseverence!
swise001 Hi, thanks for the reply and suggestions. No, I'm not using any formulas in the Excel sheet; it's all stagnant data. But It's ongoing, so I'm adding to the spreadsheet monthly. Would using the Power Pivot still work? If this is not the case, when does using RLS work? Would it have to be a SQL DB in order to use RLS in PowerBI?
Anonymous
Ok - that helps.
1. Import your Excel spreadsheet to Power BI using the default button on the home ribbon.
From here select your Excel file - and then select the sheet you want to import. If the file has more than 1 sheet - you can select more than one.
You can potentially transform your data - but for simplicity - you should be able to simply load the data into Power BI 'as-is'.
Row Level Security is defined in Power BI Desktop - on the security Ribbon:
For it to work - the data you're importing from Excel has to include some context or grouping that you can define.
There's plenty of support on Microsoft's site for creating RLS:
https://docs.microsoft.com/en-us/power-bi/admin/service-admin-rls
Although you 'create' Row level security in Power BI desktop - it doesn't actually get applied until you publish the file to Power BI Service and add users to your role membership.
At this point - now when you share this report with someone from the Service - if that user is part of the role membership that you specified - the data they see should be filtered based on your rules.
This is a huge oversimplification of the process - but it sounds like you are just trying to wrap your head around it. Hope this helps.
swise001 - Thanks for the follow-up and the recommendations. I have tried all this, however the filtering isn't working. I added someone to a Role and she's still able to see all the information that's on the page, not just the rows that I put in the role for her to be able to view. A lot of things I've been reading is that RLS cannot be used for an Excel Spreadsheet upload, so that's where my OP is spawning from. It seems silly to me that MS would have PowerBI unable to apply RLS to XLSX files...(Coincidentally, I had the RLS article pulled up and was reading it for the 20th time to see what I'm missing).
So with that said, what do I need to do with my data in Excel with the Power Pivot tool to make RLS applicable? Thank you again for your help on this.Anonymous
Don't give up too soon on this. I promise - you can get this to work.
Also remember that you have imported the data into Power BI - so the fact that it's from Excel no longer matters - as it now resides in your Power BI file.
I'll demo with a simple example:
Let's say I have this table imported in Power BI Desktop
And I want your coworker to only see the data for Florida - you could create a role specific for only Florida like this
When I import this to Power BI service - anyone I drop into this Role - will only be able to see Florida.
Importantly though - Row Level Security ONLY applies to users in a "Viewer" role of dataset workspace. - So if you invited your coworker to your workspace as a "Member, Contributor, or Admin" - he/she will not be bound by RLS.
If you still run into issues - import a few screenshots that I can reference. I'll be able to better guide what needs to be fixed.
swise001 - thanks for posting this. This was helpful for sure! However, I fiddled with it some more, and found out it was based on the fact that the person was in my workspace, so even when I had her only in an exclusive role, her status as being in my workspace overruled this and that's why the filters weren't working! Such a simple solution....Thank you for your perseverence!