Links From Power BI to Power Apps Common Data Service Record
A great dashboard in Microsoft Dynamics 365, or Model-Driven App will not only show you the relevant data and insights, it will also make it easy to get to the relevant records so you can take action.
Drill-downs and clicking on records to navigate to them has been the standard user experience in the built-in dashboards and views in Dynamics 365 and Model-Driven Apps. However, report makers must add them since a Power BI report is not connected to Common Data Service in the same way.
This experience is generally so ingrained in any user that I consider it a “must-add” for any Power BI report.
There are several different ways to link Power BI to CDS, but this blog post will focus on how to create the URL links using Power Query and a few parameters to make it easy to repurpose the same Power BI report between different CDS instances.
Find the Correct URL Syntax
If you have worked with Dynamics 365 or Power Apps for more than a few years, you will probably remember it took a little bit of work getting the right URL syntax for a specific record. Kids New users today have it easy. All you need to do now is navigate to the type of record you want, use the desired App, and the URL in the browser will give you the full string.
As the screenshot does not show the whole URL, here it is.
There are four parts to the URL:
Entity name and Record ID
Form ID - The Form ID is optional and is not always included if your app only has one form for this entity.
Most of this URL is constant except for the Record ID. This is the piece that Power BI needs to insert.
Is the App ID Needed in the URL?
While the App ID is not required in the URL, it would beis recommended in most cases.
If no App ID is defined, the user experience will vary depending on whether the user has already opened an app in the current browser session.
If an app is already in the browser session, a link without an App ID will just continue to navigate within the same app.
On the other hand, if the user is navigating from www.powerbi.com to a link in the Unified Interface, and an App ID is not a part of the current browser session, then the user will be prompted for what app to use. If only one app is available to the user, then that app will be used.
In most cases, the App ID should be defined in the URL. However, there are exceptions. For example, if users with access to a different subset of apps use the same Power BI report, then omitting the App ID can avoid the issue of adding logic to determine which app to use. Users will automatically be directed to the app they have access to or are currently using. This scenario works well when the Power BI reports are embedded in Dynamics 365 or the Model-Driven App, thus forcing the user to already have selected an app in their current browser session.
If the App ID is included in the link, then that App will be used, and possibly also switch Apps for the user if they were already navigating in a different app.
Including the Form ID in the URL is also optional. It can be used to ensure a record is opened in a specific form. For example, it is possible that the Account entity has two forms. A regular form and one for VIP customers. When generating the URL for the record, an IF statement can be added to determine which of the two forms to use based on the Account data.
Create Link in Power Query
As usual, there are many ways this link can be generated. My preference thus far has been to create the link in a separate column in Power Query and that is the example I will use in this scenario.
Note: Once the TDS Endpoint and DirectQuery becomes available for CDS, it might be better in some instances to use DAX to create the links. If that turns out to be the case, I will have to write another blog post.
I like to set up my reports so it’s easy to change out certain parameters when moving the reports between Dev, Test, and Prod environments.
For that reason, I will create the main organization URL and the App ID as parameters in my dataset. When switching environments, all I need to do is change out those parameters and all the reports will be correct again.
In the Power Query editor, click on Manage Parameters and add the two new parameters.
Add the details from the URL you copied earlier. In this case the parameters are:
CDSURL = https://YOURORG.crm.dynamics.com
APP ID = 3a240eb2-149d-ea11-a819-000d3a579c3f
In the Power Query editor, select the entity you want to create links for, go to the Add Column section and click Custom Column.
Then enter the formula below which concatenates the parameters, with the URL and record specific data. The formula is very similar to concatenating text in Excel.
The example, using the activity entity, creates the URL by inserting the parameters and field values into the URL format we copied from the browser earlier. The CDS URL and the App ID is defined by the two parameters. At the end, the [Activity Type] and [activityid] is added from the current record.
The activityid specifies the exact record, while the Activity Type determines the entity. If the URL had been for the Account table, or pretty much any non-activity table, we could have written in the entity name instead of inserting it from the current record.
Since this is the activity table, I will also add a link pointing to the regarding items (i.e. a shortcut to the regarding Account or Opportunity, rather than the activity itself).
Apply the changes and return to the report designer.
Create a table with the activities and the new URLs. This is where we need to add the relevant links.
Notice that the links are formatted as text and are not active. To change this, select the fields and change the Data category to Web URL.
Open Column tools tab
Go to Data category drop-down
Select Web URL
Now that the record link fields are formatted as Web URLs, the table should look like this and the links active.
In the layout formatting options for the table, enable the URL icon. The setting is in the Values section, but I prefer to find it just by typing “url” in the search box.
Enable the URL icon and your table should now look like this.
This icon is much nicer than the full link as it takes up a lot of unnecessary screen real estate.
However, having two URL icons in the same table can be a little confusing, so I’ll remove the regarding link and add the link as a conditional Web URL to the Regarding column instead.
Select the table.
Go to formatting
Select the “Regarding” field in conditional formatting
Enable the Web URL
Select the “Regarding URL” field in the settings
Now the Regarding title is an active link instead of an icon next to it.
This not only saves space, but the user experience is also closer to Dynamics 365 and Model-Driven Apps.
For consistency I will do the same to the Subject column, but I’ll keep the URL icon for this one.
I also rearranged the columns so the URL icon comes first. That way it will not be confused with the Regarding link.
Now I could possibly remove that final link icon to give the table more space, however if I do that, something odd happens.
Notice that now I only have one Phone Call named “Follow Up Call” instead of two as in the earlier image. The table in Power BI consolidates the rows to only unique items, so if you have two activities called the same, then only one row will show representing them both. The URL icon forces each record from CDS to be unique because it includes the GUID in the table itself. Without the link icon in a separate column I would have to include something else to make each row unique. This applies to all tables, in particular Accounts and Opportunities that often end up with identical names, so keep this in mind when creating your table or other Power BI visuals.