If you have worked with PowerBI connecting to Microsoft Dynamics CRM you will most likely have discovered a big flaw in terms of option-sets. It is not possible to retrieve the label (text) value of option-sets (both normal and global). Jukka Niiranen wrote a blog post on this very subject and how it is possible to retrieve these labels via the ‘Picklistmappingset’ entity. Unfortunately, that will only give you the system generated option-sets. Status, status reason and custom option-set labels are just not available and more often than not, PowerBI reports need categorising by these.

This means that for each data set that is built in PowerBI the user must either manually translate values to labels inside the PowerBi query builder (which impacts the performance of the query), or maintain an excel worksheet with option-set integer/label values in order to cross reference. Both are time intensive/painful to do and introduce more work each time the CRM medatata changes with respect to option-sets or status reasons.

It is currently unknown when or if this issue will be addressed by Microsoft so after a brief correspondence with MVP, XRMToolbox curator and all round super developer Tanguy Touzard, we have come up with a new tool in the XRMToolbox called the PowerBI Option-Set Assistant.

In short, the tool reads option-sets, status and status reason fields and creates records which represent each value so we can include this entity in our PowerBI queries and simply reference it. In effect, this gives us an enhanced version of the ‘Picklistmappingset’ entity that contains all system and custom option-set values (standard and global) plus the status reason values.

The user begins by clicking ‘Load Entities’ and this will display both system and custom entities from the target system. Multi-select the entities that you wish to generate records for and in the right pane you will see a list of option-set, status, and status reason type fields. Select which option-sets you would like to generate records for then hit the ‘Create records for selected option-sets’ button.

The first time the tool is run, a message will prompt the user warning that a new entity will be created in the target system called ‘Power BI Option-Set Xref’. Conversely, the button ‘Delete PowerBI Option-set Entity’ is there should you wish to remove the entity.


The records generated can be seen in advanced find, it will not be added to the sitemap as there is no need for a CRM user to amend these records. If the CRM metadata should change with respect to option-set values being added or labels amended, simply run the tool again and any new items or amended labels will be updated.

If an option-set value is deleted, the tool will not delete the corresponding record.

How to use this in PowerBI

I’ll assume you’ve connected to CRMOnline already so the first thing you need to do is select the gap_PoweBIOptionSetRefSet table along with the tables that contain your option-set columns. For this example, I’m including the Case (IncidentSet) table.

Once loaded, if we look at the ‘Relationships’ page, we’ll see our two tables but at the moment, we can’t form a relationship because the records held in the ‘gap_PowerBIOptionsetRefSet’ won’t have unique values.

As with all queries, to make them efficient we should first select only the columns we need. Click ‘Edit Queries’ then ‘Choose Columns’

 


Select only the gap_OptionsetSchemaName, gap_EntityName, gap_Label, gap_Value and optionally the gap_Language.

For each option-set, status, or status reason column that you need to retrieve the label for, you must copy and filter this query so that the gap_value column contains unique values. To do this, right click on the query in the top left of the screen and click ‘copy’ then ‘paste’ (alternatively use ‘Duplicate’).

 

In my example, I’m copying and filtering so I only include the records for the case entity & ‘CaseTypeCode’ column.

Repeat so you have one of these queries for each option-set/status/status reason column that you need to filter by. Here I’ll add one more for the ‘Cause’ which is a custom option-set.


Once you have all the queries you need to supply labels to your option-sets, delete the original ‘gap_PowerBIOptionRefSet’ query as it will be superfluous.

It is important that on the queries that are referencing our new option-set queries you expand the columns that contain the option-set value. Below, I’m expanding both the CaseTypeCode and gap_Cause on the Case (incident) query.

Once done, click ‘Close and Apply’.

Back in the relationships screen we will now see three tables representing our queries. Simply drag and drop from your source ‘option-set value’ field to the target gap_value column in the corresponding table.

 

You will now be free to create reports and use the text labels of your option-set, status and status reason fields. As a final tip, right click on the ‘gap_value’ fields on the far right of the report designer and change the name to the friendly name of your option-set field (i.e. in the below example ‘Cause’).