MultiSelect Option Set data from Dynamic365 to PowerBI

[damit23183]

Hi,

I am working on project where data are coming from Dynamic365 to PowerBI. Also, i am using XRMTool Box to get some of the lables for specific fields to PowerBI because those fields have only values (Numeric Value) available. So far, i have successfully got all labels except fields which has Type MULTISelect Option Set.

However, i am not able to get lables for fields which data type is Multiselect Option Set in Dynamic365 as you can see below (Blue Highlighted);

As you can see above, Display Name is Basis(es) that is what i am trying to get LABEL information into PowerBI. Please see more detail below how does "Label*" look like below as highlighted in blue below;

I am not able do it by using XRMTool becuase i dont see this fields in related entity. Also, Kingsway is not possible in current scenario due to high cost for client. 

Is there any otherway i can these data into PowerbI?

Thanks

[Icey]

Hi @damit23183 ,

Hope this post would help you: D365 MULTISELECT OPTIONSET POWER BI.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[damit23183]

Hi,

Thanks for your response but i have got solution by using other blog on same issue however, your solution is also little similar.

But now, i am going through another stuff which i would like to do in PowerbI to get label on tables.

For example;

I have data like these;

Source Table:

No.               Basis  --> Column Name

Case1           1,2,3

Basis Table (LookUp):

Id                          Name    --> Column Name

1                           Male

2                           Female

3                           Asia

Expected Result:

No.               Basis                   --> Column Name

Case1           Male, Female, Asia

I have joined Basis table to Primary table with One : Many relation which is only working relationship so i am only getting basis label where only one basis per case available. But, i am not abot to get as shown in Expected result above.

Any advise on Mappping or idea how would i get this thing work here.

Thanks

[Icey]

Hi @damit23183 ,

It is suggested to Split [Basis] column in Power Query Editor firstly. And then use UnPivot. 

Then, in Power BI Desktop, create relationship between the two tables.

After that, you can get this:

Or, you can create a Measure like so:

Measure = CONCATENATEX ( VALUES ( 'Basis Table'[Name] ), 'Basis Table'[Name], ", " )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[damit23183]

Hi,

Thanks for your response.

Yes i already did same step yesterday but received an error because CASE table in this scenario is connected to other Primary table with CASEID. Therefore, becuase of this splitting step i got duplicate error on CASEIDs.

Now,I am trying with MERGE which does look like working but here also i can only see those data which has some values in it. However, client want to see all Basis whether they have cases or not. In this Merge scenario, tables are not map they are isolated.

I can get all basis but will have to map table (Basis and Cases) which will give me an error.

Thanks