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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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 @Anonymous ,

 

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.

Anonymous
Not applicable

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors