Skip to content

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 proven Success Framework minimizes risk and promotes alignment to results

Explore how Catapult has helped hundreds of businesses successfully adopt cloud solutions and achieve the result they’re looking for.

  • Icon


  • Icon


  • Icon


  • Icon

    Level Up

Achieve out of this world results

Our easy-to-navigate Success Framework guides our customers through four critical stages that build towards successful adoption of a tailor made Dynamics 365 business solution


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