Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
We have a single Dashboard, but we have users across 25 different departments. When users come in we would like to show them the Dashboard with numbers filtered just to their department. Can anyone recommend a good approach?
Each approach we have considered seems to have some drawback:
If using SSRS, I suppose I would pass filters (for the Department) in as a Parameter. Looks like a simlar idea is pending for Power BI IDEA: Pass filters in URL to Dashboard. But in the interim, I am hoping someone can recommend whatever is the best approach.
Solved! Go to Solution.
@avisingh To me RLS seems the most viable approach. You've said it would be overhead to maintain RLS and I guess you're referring to ROLES that you create in power bi service for different department. However you don't have to do that ie create roles in power bi service and maintain them. Instead you can work by creating just one role add all your users in it and follow below steps.
In your data source create UserSecurity table with users email address and department (departmentID, basically identifier for department). Then join that departmentID with tables in your actual data tables. Create a single role in RLS and add all your power bi users in that role. Under RULES tab, select UserSecurity table and apply rule as [EMAIL] = Username(). Username() returns logged in power bi user email address. From UserSecurity table, departmentID for that returned username will be matched with departmentID in all your actual tables and dashboard will be filtered.
Hope it make sense.
@avisingh . Depends if there is no issue for others to accidently have access to other departments data.
Both ways I think RLS is the best option using USERNAME() as @ankitpatira states which easier to maintain than the UI of PowerBI RLS or any other solution.
Unless of course you publish pbix / excel often , then see below.
If users seeing only their department is user functionality and not data restriction you can try a clever table or card with url filters, yes you can !!
Check my post ( terrible way of explaining , but you understand easily the consept ) as it contains also the links to initial posts.
http://community.powerbi.com/t5/Desktop/Table-Visual-value-as-Filter/m-p/38393#M14227
@avisingh To me RLS seems the most viable approach. You've said it would be overhead to maintain RLS and I guess you're referring to ROLES that you create in power bi service for different department. However you don't have to do that ie create roles in power bi service and maintain them. Instead you can work by creating just one role add all your users in it and follow below steps.
In your data source create UserSecurity table with users email address and department (departmentID, basically identifier for department). Then join that departmentID with tables in your actual data tables. Create a single role in RLS and add all your power bi users in that role. Under RULES tab, select UserSecurity table and apply rule as [EMAIL] = Username(). Username() returns logged in power bi user email address. From UserSecurity table, departmentID for that returned username will be matched with departmentID in all your actual tables and dashboard will be filtered.
Hope it make sense.
Depends on what you want. Do you want them to see the other departments? If so then PowerBI does not have that functionality. If you dont want them to see the other departments or perhaps a summarised view only then use security. You can use the RLS in beta which is super easy to set up but get overridden every time you republish (please fix this microsoft). The other way is to use some DAX relying on =USERNAME() to filter the data based on the users login. Unfortunately you might need to do a fair bit of rewriting of your existing work if you do this. You will also need some sort of access rights table built up to manage it. Not the end of the world, I have done it and although it was a bit mucky to do (like you it was a fix at the end) it was not impossible.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.