Using data from SharePoint list column with multiple values in Power BI visualisation

[mrhooki]

I'm trying to create a as timeline visualisation, showing timelines by project and project members. Basically I want to show what projects project member x is involved with and who is involved in project x. Input is SharePoint list with multiple values, displayed in Power query like this:

I split the "project Members" by semi colon in (here) 5, apply some trim and clean and get:

Then I create a slicer table for distinct project member values:

Resulting in

and a slicer measure comparing names in Project member 1-5 columns to values in slicer table:

The question is: What do I need to do next to be able to visualise projects and project members the way I want ("what projects project member x is involved with and who is involved in project x")? 

[trebgatte · Accepted solution]

Another alternative is to use a more formal data modeling approach and create a cross-reference file. We show how to do this in the blog post. It may give you some different ideas on how to approach the problem.

https://marqueeinsights.com/how-to-work-with-sharepoint-multi-value-columns-in-power-bi/

Hope this helps!

--Treb, Power BI MVP

https://getstartedwithpowerbi.com 

[mrhooki]

Thanks @trebgatte this totally solved my problem.

[v-lili6-msft]

hi @mrhooki 

For "what projects project member x is involved with", 

You could create a new measure as below:

Project for each member = var _table=CALCULATETABLE(VALUES('Table'[Project]),FILTER('Table','Table'[Project Members.1]=SELECTEDVALUE('SlicerTable Project member'[Uniquemembers]) ||'Table'[Project Members.2]=SELECTEDVALUE('SlicerTable Project member'[Uniquemembers]) ||'Table'[Project Members.3]=SELECTEDVALUE('SlicerTable Project member'[Uniquemembers]) ||'Table'[Project Members.4]=SELECTEDVALUE('SlicerTable Project member'[Uniquemembers]))) return
CONCATENATEX(_table,[Project],";")

Then drag [Uniquemembers] field and this measure into a table visual.

For who is involved in project x

You just need to drag this measure[Project for each member] into a table visual and set filter is not blank

Here is a sample pbix file, please try it.

Regards,

Lin

[mrhooki]

@v-lili6-msftthanks for the effort, but I was not able to make the solution work (might very well be due to a mistake on my side) and discovered that creating a reference table solved the problem without programming/measures.