cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
avisingh Regular Visitor
Regular Visitor

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

Accepted Solutions
ankitpatira Super Contributor
Super Contributor

Re: Need to filter Dashboard for 25 different Departments

@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
Highlighted
samdthompson Established Member
Established Member

Re: Need to filter Dashboard for 25 different Departments

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.

ankitpatira Super Contributor
Super Contributor

Re: Need to filter Dashboard for 25 different Departments

@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

konstantinos Senior Member
Senior Member

Re: Need to filter Dashboard for 25 different Departments

@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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 245 members 14,746 guests
Please welcome our newest community members: