Drilling into a CRM record from a Power BI table.

Power Bi is powerful but SQL Reporting Services remains relevant One trick with SSRS  is the ability to drill into a CRM record from a CRM SSRS report with a custom expression in the drill action of a text box.

This can also be accomplished in Power BI in a very similar way.

It works for both Online and On-Premises.
Note: If your data is On-premises and you publish to the Power BI service, your deployment must be IFD (Internet Facing Deployment) in order to launch the record.

Create a query that includes the ‘Id’ field of the entity. In my example, I am reporting on Cases, so along with other fields I have selected ‘incidentid’

Navigate to an entity record and click the ‘pop-out’. This will relaunch your record and provide the complete url.

Copy and paste into notepad. What is important are the highlighted parts

1.     Your org information

2.     The Object or Entity type code of your entity (use could also use the LogicalName=incident”)

3.     The GUID of the record – dynamically supplied by the id field in the query

<img title="Drill into CRM records from Power BI" src="https://image-pobjects.netdna-ssl.com/wp-content/uploads/2017/06/062717_2021_Drillthroug3.png" alt="" />

Return to further edit your query. Select to create a ‘Custom Column’

Build out your concatenation partly by pasting from your url you pasted into notepad and partly using available columns. Use the ampersand as the connecting operator. Be sure to put double-quotes around text (“xxx”)
I named the column: URL

You should now see a field with your url

Click ‘Close and Apply’

Select your ‘URL’ column and change the Data Category to ‘Web URL’

Add the column in a table and it will be a clickable link that launches the actual record!

the long url is unattractive. We can fix that.

1.     Select the table

2.     Click on the editing paint roller

3.     Expand the values section

4.     Toggle the url icon selection to ‘On’