Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jonclay
Helper IV
Helper IV

Problem displaying Dynamics CRM Option Set labels in Power BI reports when they are separated by a ;

Hi everyone,

I'm attempting to display the values of a Dynamics 365 CRM Online Option Set in Power BI, but I'm having a problem. The Option Set values are, for some reason, hidden in Dynamics, so I only get e.g. 157420000 rather than the word Alumna. To remedy this I've created a manual table in Power BI and joined it to the Contact table where the Option Set values reside (as shown below):


pbi_optionset1.JPG


This works fine until I come across a record that has two different Option Set values. For example, a record can be both an Alumna AND a Parent, so they would effectively show an si_type of 157420000; 157420001 in the Dynamics table (i.e. the Option Set values are separated by an ;). In this case, the records do not come out on the Power BI report at all.

Of course, one way around this would be to add all the possibilities into the manual table in Power BI, but this would take forever as there are thousands.

Is there a way (possibly using DAX?) to say "if the si_type field has 2 or more values separated by ; then show both values in the Column 1 field"? An excerpt of the manully created table in Power BI is shown below:

pbi_optionset2.JPG

Many thanks,
Jon

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@jonclay,

 

In Power Query, you can split a column into rows. This would enable each row to join to your manual table.

 

DataInsights_1-1630675219354.png

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
jonclay
Helper IV
Helper IV

Hello there

Many thanks for your reply. I'll try that out later today, but it sounds as though it'll work.

Best wishes
Jon

DataInsights
Super User
Super User

@jonclay,

 

In Power Query, you can split a column into rows. This would enable each row to join to your manual table.

 

DataInsights_1-1630675219354.png

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.