Forum Discussion

smathers's avatar
smathers
Frequent Visitor
7 years ago

Dealing with Multi Value fields (ie Multi-select list or picklist)

Hi, I have seen a post similar to what I am dealing with in regards to Sharepoint and it seems PBI does cater for multi-value sharepoint fields  but I gather its Sharepoint specific. I have data that...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

hi, smathers

After my test, Does that suit your needs?

When I select B/D/E

Step1:

Use Values to create a fact table

Table = VALUES(Table1[Type])

Step2:

Create the relationship with basic table

Be careful Cardinality and Cross filter direction must like this

Step3:

Add the measure

Measure 2 = IF(SELECTEDVALUE('Table'[Type])=SELECTEDVALUE(Table1[Type]), CALCULATE(COUNTA(Table1[Type])))

Step4:

and right click Type set Show no Item data

 

here is demo pbix, please try it.

https://www.dropbox.com/s/vvvtdwe4mh5gnbt/Dealing%20with%20Multi%20Value%20fields%20%28ie%20Multi-select%20list%20or%20picklist%29.pbix?dl=0

 

 

If not your case, Could you please share some sample data with the data structure and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

 

 

 

Best Regards,

Lin

smathers's avatar
smathers
Frequent Visitor
7 years ago

Hi Lin

Thanks for going to the trouble to make that file and provide screenshots. I really appreciate it. I loved the way that multi-Select control worked with PBI, however its not quite what I was looking for.  The Multi-select list data I have in PBI is brought into the PBI file from Salesforce live link. The users of the PBI reports are not going to be selecting any values...they want to see a report on what others have been up to when visiting schools and providing resources which they enter in Salesforce.

 

I am going to give a screenshot below of the data table on the left and the report output the manager wants on the right. It is identical to the output in your example however my input is different. I cannot get away with using "IF SELECTEDVALUE(" as my resource values are not distinct for each record.

Data on left is live data

 

 

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    hi, smathers

    After my research, for your data source is live connection, It could be achieved for now. all the values is in the one field and It needs to be split. Your requirement needs to be the data source or get data by Import.

    and I have a kind of method for you to refer to.

    For example:

    Step1:

    In the Edit Queries, select RESOURCES Provided column then duplicate this column.

    Step2:

    Select the duplicate column then click Transform->Split Column->By delimiter

    Step3:

    select basic column then right click select Unpivot other columns

    get 

    Step4:

    Close&Apply and create a measure

    times = CALCULATE(COUNTA(Table1[Value]))

    Result:

    here is pbix, please try it.

    https://www.dropbox.com/s/vvvtdwe4mh5gnbt/Dealing%20with%20Multi%20Value%20fields%20%28ie%20Multi-select%20list%20or%20picklist%29.pbix?dl=0

     

    Best Regards,

    Lin

     

     

     

    • Bokazoit's avatar
      Bokazoit
      Icon for Continued Contributor rankContinued Contributor
      4 years ago

      Hi

      I am in a similar situation and that solution I thought of myself, but my problem is that I also has a value attached. So in a normal fact I will have one line pr. value, but transposing it as You do creates multiple values and then the sum will be incorrect since I would sum the value as many times as the multianswer is reproducing the line.

       

      How to do that?