Create Record Links From Power BI to Unified Interface and Dynamics 365

A great dashboard in 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, since a Power BI report is not connected to CDS in the same way, this is a feature the report maker has to add.

Also, this experience is generally so engrained in any user that I consider it a “must-add” for any Power BI report.

As usual, there are several different ways to achieve this, 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 / 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 today have it easy. All you need to do now, is navigate to the type of record you want, using the desired App and the URL in the browser will now give you the full string.

As the screenshot does not show the whole URL, here it is.

https://YOURORG.crm.dynamics.com/main.aspx?appid=3a240eb2-149d-ea11-a819-000d3a579c3f&pagetype=entityrecord&etn=account&id=c47877d8-11a1-ea11-a813-000d3a579cc6&formid=cb430327-bcc1-416d-959d-db7592e685af

There are four parts to the URL.

• Organization URL
• App ID
• Entity name and Record ID
• Form ID (optional)

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.

EDIT: Just as I thought I had tested all options, the legend, Tanguy Touzard (www.xrmtoolbox.com) reached out and let me know that there’s an easier way to manage the App ID in the URL.

Navigate to the App Designer and get the URL in the properties pane. This URL redirects to the correct URL with the App ID in it.

Now you can construct a URL to the record like this. The example is using the standard Sales Hub app.

https://YOURORG.crm.dynamics.com/Apps/uniquename/msdynce_saleshub/main.aspx?pagetype=entityrecord&etn=account&id=c47877d8-11a1-ea11-a813-000d3a579cc6

Rather than using the App ID, the schemaname of the app is inserted in the beginning of the URL. When using the link, the browser will get redirected to the matching App ID.

This approach has some benefits.
1. Same App name between Dev, Test, Prod environments. Only the organization URL would need to be changed.
2. No need to change the App ID if there has been refresh or re-install of the environment.

Only downside I have noticed is that the redirect takes a fraction of a second longer, but I doubt that is something users would care about or even notice.

The rest of the post has been augmented to include this URL construct as well and now show both options.

Is the App ID Needed in the URL?

While the App ID is not required to have in the URL, it would be recommended in most cases.

If no App ID is defined, the user experience will vary dependent on if 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 might also switch Apps for the user if they were already navigating in a different app.

Form ID

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 different 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.

Create Parameters

As I am a bit lazy, I like to set up my reports, so it is 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
• or AppName = msdynce_saleshub

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. The activities are unique since you need to indicate if you are linking to an email, or task, etc.

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).

This is very similar to the previous example, except here I am inserting the entity name of the regarding object, and its ID. See this earlier post with an example on how to query this activity table.

Using the approach with the AppName instead, the Power Query code would look like this.

Apply the changes and return to the report designer.

Create visual in Power BI with active links

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.

1. Select field
2. Open Column tools tab
3. Go to Data category drop-down
4. 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.

1. Go to formatting
2. Select the “Regarding” field in conditional formatting
3. Enable the Web URL
4. 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, 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.

Thanks for reading. Please sign up for my newsletter and follow me on Twitter Follow @CRMChartGuy