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.
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:
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:
- 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.
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:
3. Now as you can see in below screenshot Primary Contact names are added in newly created columns i.e. Primary Contact.
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.