Power BI with CRM Option Set

This article is about the workaround which a user can adopt. If user want to populated the CRM Option Set Label in report.

Power BI suite has a limitation that it shows the CRM option set Value not Label.

Please refer the previous blog(Power BI with Microsoft Dynamics CRM) for understanding how to establishing a connection between Power BI suite and Dynamics CRM for loading a data into Power BI.

Suppose, a you wants to create a report with Account Name and Region field.
Considering, Region is an option set field.

Figure 1: Creating a Table report

For getting the option set label, you need to create a dummy query where you need to map the Option Set Labels and Values.

Click on the Edit Queries button on the Menu bar. The below screen will pop up, right click on the Queries.
New Query –> Select Blank Query option

Figure 2: Create Query

A table would get created. Refer the below query format and paste it in the editor.

= #table(
{"Value","Label"},  
{  
{100000000, "APAC"},  
{100000001, "EU"},  
{100000002, "NA"}}) 
Figure 3: Table created for option set Label and Value mapping.

You can rename the table by right clicking on it. Choose Rename.
Click on Close & Apply button, select Close & Apply option.

Figure 4: Save the changes

This will load the new table with columns, and bring you back to Report.

After this you will need to establish a relationship between the Account and Region. Click on “Model” as shown in the below image in yellow box.

Figure 5: Manage Relationship between Account and Region table

Click on “Manage Relationships” button. Below screen will appear now click on “New” button for creating relationship

Figure 6: Create Relationship

Select the tables between which you want to create a relationship. Like Account and Region in my case. As I want to populate the Account Region option set label in the report.

Next click on the column which has the similar data in both the tables for mapping. Then click “OK” button.

Figure 7 : Mapping the common column from both the tables.

Clicking on “OK“. The below screen will appear where you can see the relationship which got created. Click “Close” button.

Figure 8 : Relationship established

Coming back to Model, you can now view a link between the two tables.

Figure 9 : Relationship established between Account and Region.

Click on Report and select the Label field from the Region Table.

Figure 10: Region option set Label populated.
Figure 11: Power BI Table Report