cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Daryl_K
Resolver II
Resolver II

Applying a filter to two different SharePoint lists

I have a page on my Power BI report where I want to be able to do a line comparison between two SharePoint lists. There are three columns that are needed to match a particular line between the two, and when I make a page-level filter it works on all but one of them. 

I suspect the one it does NOT work on is because on one list the data is hand-entered, but on the other list it was part of a lookup, which I then had to extract in Power Query. Both have the same structure and both columns are formatted as Text, and (if it matter) I created a relationship between the two columns, but still I have to filter each separately.

Is there something that can be done (i.e. copying to another column) so that one page-level filter will work on both lists?

1 ACCEPTED SOLUTION

My apologies for the delayed response (family emergency).

What I was trying to do was find a way that one (common) filter could work on two separate lists displayed in a Power BI report. They shared common data but even establishing a relationship between the columns that had identical data failed to allow a single filter to work on both lists.

It turns out I figured out the solution myself by using UNION to create a new table using the "common" columns, establishing a relationship between each list column and the combined (new) table, then using THAT table as a filter.

This process allows me to enter ONE data variable then it calls up all rows in both lists that have that common variable.

View solution in original post

2 REPLIES 2
v-caitlyn-mstf
Community Support
Community Support

Hi @Daryl_K ,

 

I'm not sure if I understand your needs accurately.
Have you tried to extract the data from SharePoint List and process the conversion in Power query?

Merge the columns of data and then filter them?

 

Please see if the following article is helpful to you:

Sharepoint List Extracted Data 

Shape and combine data in Power BI Desktop 

 

Hope it helps,


Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

My apologies for the delayed response (family emergency).

What I was trying to do was find a way that one (common) filter could work on two separate lists displayed in a Power BI report. They shared common data but even establishing a relationship between the columns that had identical data failed to allow a single filter to work on both lists.

It turns out I figured out the solution myself by using UNION to create a new table using the "common" columns, establishing a relationship between each list column and the combined (new) table, then using THAT table as a filter.

This process allows me to enter ONE data variable then it calls up all rows in both lists that have that common variable.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!