How To Filter Activities by Selected Users in Powe…


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:

1Power BI Report.jpeg

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:

2Power BI Report.jpeg

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

3Power BI Report.jpeg

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

Power BI.gif

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:

4Power BI Report.jpeg

5Power BI Report.jpeg

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:

6Power BI Report.jpeg

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:

7Power BI Report.jpeg

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.

8Power BI Report.jpeg

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.

9Power BI Report.jpeg

 

10Power BI Report.jpeg

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

11Power BI Report.jpeg

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.



Source link

Be the first to comment

Leave a Reply

Your email address will not be published.


*