cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Guustc Regular Visitor
Regular Visitor

CRM option set problem

I am using PowerBI desktop with a CRM2016 online dataset via the OData feed an i am having problems with the CRM option sets. Options sets are not supported in the Odata feed and they come with a numeric value which needs to be transformed or replaced manually by the value in the option set.

 

There are some topic about this on this community, in which most of the time the XRM toolbox Powerbi options set assistent tool has been named as a solution. However, the gapconsulting blog post about this is offline and i can not find any information about the tool.

 

By now I have managed to run the tool and create a option set table to import in my powerbi Application but I have problems connecting the table to my dataset. I therefore explain my problem more briefly with a picture:

 

 

Capture.PNG

I have these two tables and i need to replace the value in the potential and customercategory columns by the values in the column Optionlabel of the table Optionset. The problem is, that i can not use for example a direct relationship or an inner join because the values in each optionset are almost the same and not unique. An optionset can contain many CRM fields and many value's. 

 

How can I now get the values in the columns potential and customercateogry replaced by the Optionlabel values from the table Optionset?

1 ACCEPTED SOLUTION

Accepted Solutions
Guustc Regular Visitor
Regular Visitor

Re: CRM option set problem

I already found a solution that was actually quite simple...

Add a new formula column to the table with the option set value's and use the LOOKUPVALUE DAX formula in the table editior (not the query editor) mode and search for the gap_optionsetschema values. Next, add an extra search parameter to the formula so you can search on the field option set name AND the option set value and the right value will be filled in in this column.

 

You need to do this for each option set column, but it is quite simple and the quickest way to solve this problem.

View solution in original post

5 REPLIES 5
Moderator Eric_Zhang
Moderator

Re: CRM option set problem

@Guustc

For your dataset, I'd re-model it by "unpivot" first and then do a "merge". Check more details in the attached pbix.

 

1.png

 

By the way, posting sample data as plain text is prefred as it is time saving. 🙂 

 

Guustc Regular Visitor
Regular Visitor

Re: CRM option set problem

@Eric_Zhang thanks for your option, but this dod not work on my dataset. Really appreciating your help!!

 

Because I think your understanding might be much easier with sample data, i have added an excel and .pbix sample file to show what i would like to do.

 

Now in the table, Opportunities, i like to replace the value rows with the gap_label rows with the same value from the Optionset table. 


This is an example, the number of columns and the number op different fields an optionset can increase. 

 

Hope you can help me with this :

 

Can't attach file so here it is: https://ufile.io/4ed7c

 

Guustc Regular Visitor
Regular Visitor

Re: CRM option set problem

Anyone an idea based on the example file that was posted?

Guustc Regular Visitor
Regular Visitor

Re: CRM option set problem

I already found a solution that was actually quite simple...

Add a new formula column to the table with the option set value's and use the LOOKUPVALUE DAX formula in the table editior (not the query editor) mode and search for the gap_optionsetschema values. Next, add an extra search parameter to the formula so you can search on the field option set name AND the option set value and the right value will be filled in in this column.

 

You need to do this for each option set column, but it is quite simple and the quickest way to solve this problem.

View solution in original post

PL1976 Visitor
Visitor

Re: CRM option set problem

Thanks for answering. Do you have an example?

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 95 members 1,257 guests
Please welcome our newest community members: