cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors