Working on Lookup field of Dynamics 365 in Power BI

By | October 22, 2018

Introduction:

Recently, we had a business requirement to show lookup fields name in our Power BI Reports. In Power BI Lookup columns show its guid and not name. So after some research and play around we found a solution to this problem.

We achieved it by using LOOKUPVALUE function.

Here as you can see in below screenshot Primary Contact is showing with guid and not directly with name on Account table, so to show lookup value with name instead of guid, we can make use of LOOKUPVALUE function in our report.

Working on Lookup field of Dynamics 365 in Power BI

Steps to show Lookup name:

1. At first Add new column for the lookup field. In Our case field is primary contact. please check below screenshot:

Working on Lookup field of Dynamics 365 in Power BI

2. Now we can set LOOKUPVALUE function on our newly created column above, as you can see in our below screenshots:

  • Once we added new column in table it looks like as below:

Working on Lookup field of Dynamics 365 in Power BI

  • Now in the second step, we need to implement LOOKUPVALUE function in our column. You can also change the name of column. In our case we have changed it with Primary Contact.

Working on Lookup field of Dynamics 365 in Power BI

a. LOOKUPValue(Target table result field, Target table key, Source table matching key as on Target table)

b. For Example In Our case the expression look like below: LOOKUPValue(contacts[fullname],contacts[contactid],accounts[_primarycontactid_value])

  • Once done with it we will save it by clicking on icon highlighted in red rectangle below:

Working on Lookup field of Dynamics 365 in Power BI

3. Now as you can see in below screenshot Primary Contact names are added in newly created columns i.e. Primary Contact.

Working on Lookup field of Dynamics 365 in Power BI

Conclusion: 

With the above simple steps and using LOOKUPVALUE function we can show data from other table easily without having any relationship, but make sure that there should be common key present in both tables.

One Pic = 1000 words! Analyze data 90% faster with visualization apps!

Get optimum visualization of Dynamics 365 CRM data with –
Kanban Board – Visualize Dynamics 365 CRM data in Kanban view by categorizing entity records in lanes and rows as per their status, priority, etc.
Map My Relationships – Map My Relationships – Visualize connections and relationships between Dynamics 365 CRM entities or related records in a Mind Map view.