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:
Step 2. Click Load Entities > Select the Entity
Step 3. Select the option set you wish to use in Power BI
Step 4. Click Create records for the selected Option Sets
You may see this message the first time you do it. Click YES
Step 5. Now in Power BI choose to get data and load the gap_powerbioptionsetrefs data set
Step 6. Now you need to join the Account set with the PowerbiOptionset. On the account select ‘merge queries’ and click OK.
This will create a new column. Just add the column and you are ready to go!
