Applying a filter to two different SharePoint lists

[Daryl_K]

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?

[v-xiaoyan-msft]

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.

[Daryl_K · 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.