Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Need to filter Dashboard for 25 different Departments

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:

  • Make 25 copies of Dashboard (one for eachdepartment): Lot of overhead to maintain 25 copies of the Dashboard
  • Pin a Report Page with Slicer to the Dashboard (so that users can make a slicer selection to choose their department right from the Dashboard): Disadvantage is that a pinned report page looks horrible in the Mobile App
  • Content Pack: Gosh it is a LOT of work for 25 different department, to import the Content Pack, go to the report, apply the right filter then pin elements to their own version of Dashboard and then share again. And of course if we update the original dashboard...oh gosh!
  • Using Row Level Security: We're still exploring this. It seems there would be overhead to maintain Row Level Security

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.

-Avi Singh@PowerPivotPro

1 ACCEPTED SOLUTION
ankitpatira
Community Champion
Community Champion

@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.

View solution in original post

3 REPLIES 3
konstantinos
Memorable Member
Memorable Member

@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

 

 

Konstantinos Ioannou
ankitpatira
Community Champion
Community Champion

@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.

samdthompson
Memorable Member
Memorable Member

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.

// if this is a solution please mark as such. Kudos always appreciated.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors