Dynamics 365 Option Sets and Power BI
Every “nudging” article is meant to nudge you and technology in a certain direction, with the goal to improve business and technical decision making. Technology is as perfect as the Homo Technicus* behind it, let’s nudge it!
Option sets in Power BI
The Dynamics 365 connector exposing data into Power BI only shows the label values for option sets. If we create any report in PBI using the option set values only, the report is not readable. If we include a table that spells out the values with their respective labels, we would need to update the report definition every time labels/values are modified, or new ones are defined. À propos changes, most iof us believe that changing an option-set label and value takes very little execution time, as long as the overarching side-effects of that change across all the components that reference the option set have been thoroughly reviewed and decided upon. For a humorous take on that type of change and its lifecycle, see the Tale of a Small Change.
Use an OData direct connection to the option set, so that the labels and values are refreshed from the source. Be forewarned though that this solution can be used for global option sets only.
Most option sets are in fact dimensions for the different measures incorporated in the report.
Global option sets are maintained in one place and the modifications are propagated to all their references. This ensures the accuracy of the data slicing across that dimension, since the same dimension values + labels will be consistently defined across multiple entities.
I am going to exemplify this solution using the Case entity and the Case Origin global options set.
In the PBI Desktop app, create a new D365 connection to the Case entity and select a few fields from the case, including the Case origin (incident_caseorigincode).
Create a new OData connection using the Web API endpoint, and appending the following string “/GlobalOptionSetDefinitions(Name='incident_caseorigincode')”. In the MetadateID table, click on the Options table hotlink.
Expand the Label column and select LocalizedLabels, then expand one more time, selecting the Label and LanguageCode fields. The end result of the two expansions should look like this:
Add another query containing cases, and notice that the caseorigincode field is exposed and only the values of the option set are shown. Create a relationship between the caseorigincode on the case and the Label.LocalizedLabels.Label column to be able to translate the value to the corresponding label, using this formula lblOrigin = RELATED(CaseOrigin_gblOS[Label.LocalizedLabels.Label]).
Here is how the final chart looks like:
Every time the current labels of a global option set are modified or new labels are added, refreshing the chart will update the underlying model with the latest structure and the chart will display all the labels correctly.
Of course, if you need to support multiple languages in the PBI charts, you could use the option sets inherent translations, conditional logic and the LanguageCode to decide in which language to display the labels in the chart. Watch for the next nudge with more details on multiple languages.
* In his bestselling book Nudge, Richard Thaler (winner of the 2017 Nobel Prize in economics) introduced the concept of Homo Economicus – humans thinking and choosing unfailingly well from an economical perspective. In this series of blogs, I have extended this definition to Homo Technicus – humans thinking and choosing unfailingly well from a technical perspective.