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:
How to get started:
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.