Forum Discussion
Relationship when a variable is a list with multiple values.
Hello everyone,
Originally, my Sharepoint List had an ID field that was related to another List. For example, List A shows that "Paul" is currently working as a "01", which is the value "Analyst" in List B.
Now my ID field contains multiple values to track many information in the same field. List A shows that "Paul" was a 01, 05, 07, which correspond to "Analyst", "Developper", "Architect" in List B.
In Power BI, my report was originally showing the correct result of the relationship : Paul = Analyst
Now that my ID field is now a list of values, how can I make my report show that : Paul = Analyst, Developper, Architect ?
I know that I can extract the values from the table and insert a comma in between, but I'm not sure how to get the corresponding values in List B from the relationship... which was easy when there was only one value. Since I don't know how many different value an individual can have (1 job or n jobs), I'm not sure where to go from here.
To summarize : I have a field in my BI that is a list of IDs where the corresponding value is in another table. I want my report to show the N values from this field with the correct associated text.
Let me know if you have questions to help make this clearer.
Cheers.
OK we solved it by splitting the values in the list in different columns, unpivot the table, create a request to replace the ID by the value and then contact all the values in a field.
3 Replies
Hello Lonepaw ,
you do a relationship between these 2 tables on the ID field.
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Hello Idrissshatila
Originally I had a relationship between the 2 tables on the ID field and the single value was showing without issues.
With the same relationship, now that my ID field is a list of values, my report is showing a blank field. So there must be manipluation (Power Query ?) to do in the request for this new structure of the field.If I transform to extract the values with a comma in between, only the results with one value are showing in the report, everything else is blank.
Thanks.
In my BI if I only do a relationship between the list in the ID field and the value of table B, the field is empty in my report.OK we solved it by splitting the values in the list in different columns, unpivot the table, create a request to replace the ID by the value and then contact all the values in a field.
