Forum Discussion

damit23183's avatar
damit23183
Microsoft Employee
6 years ago

MultiSelect Option Set data from Dynamic365 to PowerBI

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

Need Help

4 Replies

    • damit23183's avatar
      damit23183
      Microsoft Employee
      6 years ago

      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's avatar
        Icey
        Community Support
        6 years ago

        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.