Ultimate Guide: Mastering Month-End Closing With Microsoft Dynamics NAV 2017

Download

There is an issue when connecting Power BI with Dynamics 365 and working option sets. They return the numeric value instead of the label.  This presents a problem when trying to create reports. Instead of seeing labels for Industry type, you get a list of numbers that are transposed in the charts – something that is not very intuitive at all.

It is possible to create a conditional column and use ‘if’ statements to output the correct associated label but that is very time consuming and ineffective.

A better way is to make use of the ‘Power BI Option-Set Assistant’ by GAP consulting found in the XRM tool box. The tool creates a custom lookup in CRM. You can use Power BI to join the tables and pull the option set labels into the reports as required.

Here’s how to use the Power BI Option Set Assistant:

  • In the XRM tool box, navigate to the Power BI Option Set Assistant and open the plugin:

Power BI Option Set Assistant

 

  • Click Load Entities > Select the Entity

Load Entities

  • Select the option set you wish to use in Power BI

Option Set for Power BI

  • Click Create records for the selected Option Sets

Create records for selected option sets

  • You may see this message the first time you do it. Click yes.

 

Power BI Option Set Warning

  • Now in Power BI choose to get data and load the gap_powerbioptionsetrefs data set

Load Power BI Option Set Data set

 

  • Now you need to join the Account set with the PowerbiOptionset. On the account select ‘merge queries’ and click OK.

Merge Account set with Power BI Option Set

 

  • This will create a new column. Just add the column and you are ready to go!

 

Add column in Power BI

Like What You've Read? Subscribe to our Newsletter!

 

 

 

 

 

Leave a Comment

Your email address will not be published. Required fields are marked *

*

Filter:

Archives

Register to recieve the latest ERP & CRM insights