How to Use Dynamics Optionset Labels within PowerBI

 

One of the current limitations of using PowerBI with Dynamics 365, is that it will not automatically map your OptionSet values with labels. For example if you have defined OptionSet values for Gender as 1 = Male, 2 = Female within PowerBI visualisations this would appear as 1 and 2 rather than Male and Female. Thankfully Gap Consulting have created a nice XrmToolBox plugin called the “PowerBI Option-Set Assistant” to work around this limitation.

How it Works

The PowerBI Option-Set Assistant creates a new entity within your Dynamics Online instance called “gap_powerbioptionsetrefs” and populates it with a data map that contains your OptionSet value along with the associated OptionSet label. Which you can use to build queries (left-join) within PowerBI. Once you have the tables related within PowerBI you can then take advantage of the label field within your visualisations.

This will allow you to go from this:

To this:

How to get started:

  • First download XrmToolBox (http://www.xrmtoolbox.com) if you haven’t already, and then install the “PowerBI OptionSet Assistant” plugin.

  • Open PowerBI Option-Set Assistant and connect to an Dynamics 365 instance.

  • Load Entities your entities within the plugin:

  • It will automatically filter on OptionSets to your right of screen. Select an OptionSet you wish to create mapping records for, and click “Create records for selected option sets”

  • Now, open PowerBI Desktop
  • Open an existing or new data source in PowerBI and add the newly created gap_ powerbioptionsetrefs entity to your dataset.

  • (Optional) Create Query to filter on optionset name if you have repeating gap_values and give it a new name (e.g. gap_powerbioptionsets_youroptionsetname)

  • Now click on Manage Relationships
  • And create a New relationship

  • In the drop down select the Entity followed by Optionset value. Then select the gap entity followed by the gap_value attribute. When done click OK.

Now within any of your visualisations to use the label instead of the value:

Instead of using the actual attribute name, you replace this with the label. This is how it would have looked prior to the mapping:

This is how it should look after.

Hopefully you found this blog helpful in getting around this particular issue, hopefully in subsequent versions of PowerBI this won’t be necessary. If you have any queries or comments please shoot me an email, tweet me on Twitter, or message me on LinkedIn. Please subscribe if you like to see more articles similar to this in the future. Thanks for reading! Happy D365’ing.

5 Replies to “How to Use Dynamics Optionset Labels within PowerBI”

  1. I have followed this guide and got it to work however- when I refresh the data all my gap_value disappears. What could be wrong?

  2. All my option sets have same relative values so it can not create a unique key to join.

    Example I have multiple lookups where the first value is the Id: 800320000.

    Do I need to go change all of the values in the option sets to be unique or is there a different approach here?

    Thanks,

    Drew

    1. Hi Drew,

      See the optional step above:

      Create Query to filter on optionset name if you have repeating gap_values and give it a new name (e.g. gap_powerbioptionsets_youroptionsetname)
      Regards,

      Andrew

Leave a Reply

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