I recently returned from a trip where I traveled around Europe teaching Business Intelligence and Power BI for Dynamics 365 and finished it off with a number of presentations at eXtreme365 in Lisbon. First of all, a huge thank you to all the people who attended my sessions and all the great questions that followed.

One question that kept coming up was how you would put a “contextual” Power BI tile or report on an account form in Dynamics 365. So on the account form, you would have some nice Power BI visuals, specifically for that customer. The simple answer is that you don’t, but there are some options that can get you close.

First of all, the reason I say that you don’t is because Power BI does not allow direct iframing of its components. If you want to iframe Power BI visuals there are some possible options you need to look into.

The “Power BI Embedded” service in Azure.  This service is intended for external sharing of your Power BI visuals and will cost you approx. Five cents every time someone looks at them. If your purpose is using Power BI in the context of Dynamics 365, then Power BI Embedded is not what you are looking for.

The other option is to get a developer to create the Azure security tokens and authorizations required in your iframe to make it work. Documentation, with a video for that process is available here.

Finally, you need to take into consideration that Power BI is not specifically designed for reporting in context of customer profiles. On the other hand, that is exactly what Dynamics Customer Insights is designed to do, so I’d highly recommend looking into this service prior to attempting to implement this feature with Power BI.

Dynamics Customer Insights, DCI, formerly known as Azure Customer Insights will be available with the next release of Dynamics 365. Disclaimer: The release was indicated during the keynote at eXtreme365, but to my knowledge not explicitly promised.

Now that we have that out of the way, how can we create Power BI reports filtered to a specific account or another Dynamics 365 record and only one click away?

Create a URL with filter parameters for your report

The process is simple. Rather than having the Power BI report directly on the form in Dynamics 365, we will have a link that will open the filtered report in a new window.

I’m going to assume we already have a report in Power BI we want to use for this purpose.

Navigate to the report on http://www.powerbi.com and grab the unique URL for the report page.

You can filter this report by adding a few parameters to the end of the URL in this format.

?filter=Table/Column eq ‘guid’

For example

?filter=Account/accountid eq ‘329874328fsh9843298743298’

Note the quotes are single quotes and the table and column names are case sensitive and must match exactly what you have in Power BI. Spaces in the table and column names are prohibited so do not rename “accountid” to “Account Id”.

You can try out the URL you’ve constructed in the browser first to see if it filters the report as intended.

Create the URL in Dynamics 365

Now we have the unique URL for the report and the filter parameters we need.

In order to put the filtered URL on a form in Dynamics 365, we need a field that contains our URL and inserts the GUID of the record in it.

As Dynamics 365 does not let us use the GUID in a calculated field, nor in the processes, we will need a custom workflow process that gets the GUID and adds it to a custom field.

I used Workflow Elements by Aiden Kaskela for getting the record GUID.

First, create a field on the account called GUID. Make it a single text field.

Create a workflow to get the GUID of the record and insert the GUID in the new field.

On the update account step, use the first step of getting the metadata to insert the GUID.

Now, run the workflow on all your accounts, so make sure the workflow is available as On Demand.

Ok, now we have a separate field with the GUID for all the accounts in Dynamics 365.

Back on the account entity, create a new single line of text calculated field for the URL.

Set the calculation to concatenate your unique report URL with the GUID field that you just populated.

This is the field that grabs the GUID and creates the filtered URL for the specific Power BI report.

Add the field to the form and publish your changes.

Try out the new link and the filtered Power BI report will pop out in a new window when you click on it.

Enabling for Views

However, if you add the link to a view the filtering does not work. This is due to the spaces, slashes and single quotes in the URL. If you use URL encoding for those characters instead, the link will also work from the views.

Replace the following:

• space with %20
• / with %252F
• ‘ with %27

Here’s how it looks in the calculated field editor.

And now the links are functional in the views as well.

Comments

There’s an assumption that the users have access to the dataset and the report in Power BI that you are referencing, so make sure it is shared.

Note that this approach only works on reports and not dashboards. Dashboards in Power BI does not support filtering, although the individual tiles can be filtered prior to being pinned.

There are more details on URL filtering on the Power BI blog, including details on how you can use it to ensure a filter is preserved when you click on a dashboard tile and go to the report itself.

As I mentioned in the beginning, this approach is more of a workaround. If you want the tiles directly on the form itself, then you’d need to investigate the security token flow and get a developer to set it up. However, I’d encourage investigating Dynamics Customer Insights prior to going that route.

The workaround described here is something you can get up and running in 30 minutes or less assuming you already have the report you want to use.

Adding the standard Dynamics 365 charts to the form is also an option and could definitely be a viable solution, especially since less records are usually being queried when the charts are in the context of another record and only need to display related data.

Hope you found the tip useful. As always please sign up for my newsletter and follow me on Twitter. Follow @CRMChartGuy