Using the Power BI Option-Set Assistant with Dynamics 365 - Catapult
Catapult brand logo
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

Catapult reinvented consulting and implementation services for the subscription economy.

Subscription packages that include everything you need to get up and running, and transform your business

  • Icon

    Lean
    Deployment

  • Icon

    On-Demand
    Training

  • Icon

    Platform
    Upgrades

  • Icon

    Amazing
    Support

  • Icon

    Software
    Licensing

Packages from

$2,000

/mo

36-month commitment

Get Pricing

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