Power Community

Power Community

Showing multiselect option set from Model Driven Apps in Power BI

https://ecellorscrm.com/wp-content/uploads/2024/03/power-bi-multioptionset.png

Hi Folks,

Well, this post will show you how you can work with multi option sets from Dynamics 365 in Power BI. First of all, you need some basic understanding of Power BI Desktop to follow. However, I made it clear for people with little background to follow and relate to. I scanned through the internet, but I couldn’t find a similar post, hence I am blogging this if it might help someone. I have faced this issue and here is the solution, you don’t need to use XrmToolBox nor Postman nor complex Power Query as many out in internet would suggest.

So, follow with me along, if you were trying show the values in Multi OptionSet from Model Driven Apps in Power BI as below, then this post is absolutely for you.

Practically if we retrieve the value of Multi OptionSet field as shown in the above image. You get something like below in comma separated values.

Now based on use case and the requirement, we need to transform our data, i.e. Split the values into rows or columns using a delimiter, in this case, we use comma as delimiter. Here I am splitting into multiple rows as I need to show the contacts for different option values selected in the record.

Select on the respective field and choose Split Column option available in the ribbon.

Next, you will be presented with Split Column Delimiter Dialog box, you may select the options as below and click on Ok.

Next in the Split Column by Delimiter, choose as below.

Once clicked on Ok, now the Multi OptionSet was changed to Single OptionSet and showing the values in different rows.

We can use Dataverse REST API to get the OptionSet values as below in Power BI, click on Get Data –> Web, enter the below in the URL to get the MultiSelect OptionSet Values –> Load. You can refer here some reference.

https://ecellorshost.crm5.dynamics.com/api/data/v9.2/stringmaps?$filter=attributename%20eq%20%27powerbi_multioptionset%27

Once data is loaded, it should look as below..

So, now click on Close and Apply the transformation to be saved in the model, later create the data model relationships by going to the model view as below between the multiselect OptionSet field in the contact table and string map table.

Once the relationship is established, we can proceed with plotting the visuals in visuals of your choice. For simplicity, used.

Hope this helps someone looking out for such requirement which at least could save couple of seconds.

Cheers,

PMDY

Author: Pavan Mani Deep Y

Technology geek who loves sharing my leanings, quick tips and new features on Dynamics 365 & related tools, technologies. An Azure IOT Enthusiast….

This post was originally published on this site

- Advertisement -spot_img

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisement - Advertisement

Latest News

5 Benefits of In-App Notifications for Microsoft Dynamics 365 CRM users

For a successful sales process, you need to stay up-to-date with crucial sales information like deal closures, opportunities won,...

More Articles Like This

- Advertisement -spot_img