Tip #687: OptionSets in Power BI

OptionSetIf you’ve been playing working with the Dynamics CRM content packs for Power BI, you probably know that for custom OptionSets attributes you get only the numeric value and not the associated label.

If you read our tip #684, you know it’s probably because Power BI does not send annotation preferences to CRM. The team does recognize this gap and is working with the Power BI team to provide a long-term solution.

In the meantime, the workaround is to use temp tables for OptionSets and merge your CRM data with these tables:

let 
  Source = #table(
    {"Value","Option"},
    {
      {0, "No Committed Budget"},
      {1, "May Buy"},
      {2, "Can Buy"},
      {3, "Will Buy"}
    })
in
  Source

How do we know what to enter, and how to define and use these temp tables? Content Pack PBIX file has all the answers and David “Xrm.Tools” Yack has a nice post about PBIX content and internals.

Tweet about this on TwitterShare on Facebook3Share on Google+1

3 thoughts on “Tip #687: OptionSets in Power BI

  1. Rob Boyers says:

    Or you could use the PowerBi OptionSet Assistant in the XRMToolbox

    • Yes, Rob, the PowerBi OptionSet Assistant has been mentioned in David’s post so I didn’t include it. In a hindsight, probably should have. It certainly does address the issue and is a good stop-gap until the problem is fixed. Manual definitions are a good solution if you’re only after a handful of the optionsets (rarely the case though!)

  2. Z says:

    Wouldn’t this mean anytime the business changes the label in CRM that the static data would also need to be updated?

Leave a Reply

Your email address will not be published. Required fields are marked *