Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mrhooki
Regular Visitor

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

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:

multiple values_1.png

 

 

 

 

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

 

multiple values_2.png

 

 

 

 

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

multiple values_5.png

 

 

 

 

 

 

 

 

 

 

 

Resulting in

 

multiple values_6.png

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

multiple values_4.png

 

 

 

 

 

 

 

 

 

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")? 

1 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 

View solution in original post

4 REPLIES 4

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 

Thanks @trebgatte this totally solved my problem.

v-lili6-msft
Community Support
Community Support

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

8.JPG

 

 

Here is a sample pbix file, please try it.

 

Regards,

Lin

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

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.