Power BI is a data analysis and visualization tool which provides various controls for this such as different types of charts, slicer etc. Using these controls user can analyse and monitor organizations or business growth easily.
However recently we had a business requirement where we were supposed to show the Case records in a table and open them directly in Dynamics CRM from the Power BI report.
Below are the steps to accomplish the same:
1. At first, we will need the case records required to be shown in the table in Power BI report and for that create a dataset of Case entity records in Power BI.
Kindly refer to our earlier blog to understand how to create a Power BI Report.
2. To add URL as a column in the Power BI table you need to follow below steps.
- Open the “Edit Queries” under the “Home” tab.
- Create a new column which will hold URL of each record based on GUID of record.
- Specify the name of the column and Paste the CRM URL in the “Custom column formula” below the column name.
- Add CRM URL and entity type of the record to the “Custom column formula”.
- Now insert “incidentid” column of “Case” entity displayed in the tab “Available columns” besides the “Custom column formula”.
- Click on “Close & Apply”.
- You can see “URL” column gets created in the “Case (i.e. incidents)” dataset.
- Change the “Data Category” of the column from “Text” to “Web URL” to treat it as a URL and not a simple text.
- Now drag and drop the “URL” column from “Case (i.e. incidents)” dataset in the table grid.
- Use URL icon instead of having long URL in table as shown the screenshot below and see the changes in the table.
3. Save and publish Power BI Report.
4. Now open Power BI Report in CRM dashboard.
5. Click on URL of Case which you want to open.
6. Case record which you have selected gets opened in the new tab.
Using the steps above user can quickly open entity record from Power BI report in Dynamics 365