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:
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.
After selecting the tables from the from appropriate environment, you can either load the data directly or choose to transform it.
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:
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:
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:
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.
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.
Step 7: Finally, we will be able to filter Activity by Users as shown in the below screenshot.
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.