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
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-xiaoyan-msft
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
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.