How To Filter Activities by Selected Users in Power BI Reports?

By | September 14, 2023

Power BI is a powerful tool for visualizing and analyzing data in graphical or chart formats. It is widely used to create interactive dashboards and reports from various data sources.

In this blog post, we will explore how to create a Power BI report that filters activities based on selected users. This means that only activities owned by the chosen users will be displayed in the report.

To achieve this, we will need to handle some relationships between tables due to the complex structure of activity data in CRM.

Here are the steps to accomplish this requirement using Power BI:

Step 1: Begin by connecting Power BI Desktop to Microsoft Dataverse. To do this, navigate to Power BI Desktop and look for the ‘Get Data’ option. Expand ‘Get Data’ and click on ‘Dataverse’.

Please refer to the screenshot given below:

Power BI Report

You will be prompted to log in with your user credentials to establish the connection to Dataverse.

Step 2: After the connection is established, select the necessary data from Dataverse. When you click on ‘Dataverse’, you will see an environment selector pop-up window. Choose your environment from the options provided.

Please refer to the screenshot given below:

Step 3: Now, select the required tables as indicated in the screenshot.

Power BI Report

After selecting the tables from the from appropriate environment, you can either load the data directly or choose to transform it.

Power BI

If you wish to include only specific columns from the table and avoid unnecessary data, use the ‘Transform Data’ option. This will open the Power BI Query Editor, where you can specify the columns needed for your Power BI report.

In this scenario, we require data from the ‘systemuser’ and ‘activitypointer’ entities.

As to have only required data in report and not to load the report with unnecessary data, we can use “Choose Columns” option to select only required columns from the table.

Please refer to the screenshot given below:

Power BI Report

Power BI Report

Step 4: Click the ‘Close & Apply’ button to update the changes in the tables. You will now see all the table columns in the sitemap.

As shown in the screenshot below:

Power BI Report

You will be able to see all the Table columns in the sitemap. Select Table from Visualization, then select the columns Activity type, Subject from Activitypointer table.

As shown in screenshot here:

Power BI Report

Step 5: To create a meaningful report, we need to add a slicer visual control to filter the data based on user selection. Select the ‘Full name’ column from the ‘User’ table.

Power BI Report

Step 6: To reflect the slicer’s selection within the table, we must establish a relationship between the ‘Users’ and ‘Activity’ tables.

Navigate to the Modeling tab and look for ‘Manage Relationships’.

Click ‘New’ to create a relationship. Select ‘Ownerid’ from the ‘activitypointer’ table and ‘systemuserid’ from the ‘User’ table to establish the relationship.

Power BI Report

Power BI Report

Step 7: Finally, we will be able to filter Activity by Users as shown in the below screenshot.

Power BI Report

Conclusion

By following these steps, you can create a Power BI report that displays activities filtered based on selected users. This is a valuable way to present data for various business scenarios within Dynamics 365 CRM.