Say adiós, farewell, goodbye, good luck and so long to the mundane task of making Power BI dashboard copies and say hello to one personalised Project dashboard to rule them all with Power BI.
If you’re a Project Manager or use the Power BI Projects Overview dashboard to keep track of your projects and you want to avoid the unnecessary overheads like development, maintenance and security associated with making Power BI dashboard copies – keep reading!
Projects often span multiple teams, sponsors, departments, resources and locations, and information frequently needs to be shared with the resources involved in those projects. At Sensei, we’ve received a few requests from our clients to create copies of Power BI dashboards so that they can be shared with different teams, and modified, so that resources within those teams can only see data for the projects they belong to. In this blog post we will show you how to easily create one Project dashboard to rule them all in Power BI and share it with resources in different teams or even your entire organisation, ensuring everyone sees information only for the projects they are involved in.
2. Things we’re assuming
- You have a Power BI Pro subscription. We’re going to utilise a Power BI feature called Row Level Security (RLS) and it’s only available with a Pro subscription.
- You have basic knowledge of Power BI and its sharing mechanism.
- You have used the Project Online OData service before to query and retrieve sample data to build a dashboard.
- You understand basic DAX expressions.
3. Row Level Security in Power BI
Row level security (RLS) is a popular business intelligence technique that is used to limit access to data i.e. a filter table that provides data to the visuals based on the user’s access level. In Power BI, RLS is defined through the creation of roles that contain information for rules. The process of setting up RLS in Power BI is shown in the below image.
4. Projects Overview Dashboard
Consider the sample Projects Overview dashboard shown below. This dashboard gives an overview of projects by various measures. You can also can use the “Slicer” feature to filter information by department. However, anyone this dashboard is shared with has full access to the information and can see and filter without restriction. For example, if you’re a project manager using this dashboard within your organisation and sharing it with project resources that are part of Engineering team, they would also be able to see information for projects that belong to IT team.
Now let’s look at how we can use the RLS feature in Power BI to create a single dashboard that caters for different teams so that everyone can only see the information based on their access level or the projects they’re assigned to.
5. Define roles and security in Power BI Desktop
The first step towards setting up RLS is to ensure ‘Resources’ are mapped to ‘Projects’. To do this, we will use the OData feed service from Project Online or Project Server to import the Projects, Assignments and Resources information into Power BI. This step will identify which resource belongs to which project.
Once the data is imported and the report is built, the next step is to define the role for RLS via the ‘Manage Roles’ option.
Here we will use the RLS feature, ‘DAX Expression’ to filter the Resource Table, using a specific Username function of DAX to identify which Resources require filtering.
The above setup enables Power BI to firstly use the DAX Username function to identify the logged in user, secondly, match the user to the email address column within the Resources table and thirdly, filter the Resources table. As the Power BI model has an active relationship between the Resources, Assignments and Projects tables, we only need to use the DAX Username function to filter the Resources table (the filter will also be applied to the Assignments and Projects tables.). We now have filtered dashboard visuals that only show information for departments and projects that resources belong to!
It is important to note that the Username function in DAX returns the identity of a logged in user. Therefore, in the Power BI service portal, a user logs in with their email address so it returns the email address of a user as its identity. That’s why we’re using the email address column within the Resources Table – to match the value returned by the DAX Username function. In case your organisation’s Power BI login is different from the email address, you will need to use the ResourceNTAccount column instead of the ResourceEmailAddress column for the mapping as shown in Step 3.
We are now ready to save the Power BI desktop file and publish! After publishing the dashboard to your Power BI service, you can share the dashboard by adding Resources as a member to the role created in Power BI desktop.
If you’re sharing the dashboard with your entire organisation, adding a ‘Member’ to a ‘New Role’ enables you to specify distribution lists or security groups instead of individual resources.
Now that RLS is built and configured, it is time to test it. The table below demonstrates how you can view the dashboard as it would appear to the user whose email address you have provided. This is a quick and easy way to check the dashboard is set up correctly.
Finally share the dashboard with individual resources or entire organisation and let Row Level Security in Power BI do its magic of handling security to show resources only what they are entitled to.
Ankit Patira, BI Specialist & Reporting Revolutionary at Sensei
Ankit is Sensei’s Power BI guru and specialises in Microsoft BI technologies, cloud based BI and self-serve and data analytics. Hailing form a development background, the data and technology enthusiast has extensive experience across a variety of industries and markets and takes pleasure in revolutionising your reporting with dynamic, easy to understand and most importantly – meaningful insights.