Article

Using the Power BI Option-Set Assistant with Dynamics 365

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:

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

Power BI Option Set Assistant

Step 2. Click Load Entities > Select the Entity

Load Entities

Step 3. Select the option set you wish to use in Power BI

Option Set for Power BI

Step 4. 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

Step 5. Now in Power BI choose to get data and load the gap_powerbioptionsetrefs data set

Load Power BI Option Set Data set

Step 6. 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
Register to receive the latest Dynamics 365 Insights

Our Success Framework guides customers through their digital transformations.

Explore how Catapult can help with each critical stage on your journey to out of this world results with Microsoft Dynamics 365.

  • Icon

    Learn

  • Icon

    Load

  • Icon

    Launch

  • Icon

    Level Up

Build and price a Dynamics 365 solution that’s right for your business with our powerful pricing tool

Get a Custom Quote

Catapult Team

Establishing a clear understanding of what you want to get out of the system is key. The rest is down to clear communication and a willingness to learn and adapt to change.

Abdullah Osman, Project Manager