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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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