VBA code to filter pivot table by 2 separate lists

[palmeirense_]

Hi,

I have a pivot table connected live to a Power BI dataset, which shows a list of product sales by store location by day.

Our company ran a promotion on a few products at certain store locations for a particular period of time.

I need a code that would let me filter this pivot table on only the stores and products where this promotion was happening, by using the values in column A for both "Store List" and "Product List" sheets.

I've seen solutions that let me do this filtering by list but not when connected to a pivot table.

Attaching an example for reference

Thank you!

[Joske920]

Hi,

I used the power pivot data model to do this

first add the sales list to the data model
add the filter tables to the data model
make relationships
add a true or false column in de sales table with a formula

make a pivot table based on the data model table

check the file so might make more sense

[palmeirense_]

Hi,

I used the power pivot data model to do this

first add the sales list to the data model
add the filter tables to the data model
make relationships
add a true or false column in de sales table with a formula

make a pivot table based on the data model table

check the file so might make more sense

Hi, thanks for the reply. It doesn't seem to work in my case. When I try to add the pivot table to the data model I get an error message saying:

"The selected range is invalid. Select a valid range. A table cannot overlap a range that contains a PivotTable report, query results, protected cells or another table."

[Joske920]

did you look at the attached file?

go to the data model and look at the tables

you cant add a pivot to the data model
a pivot is based on a table, not the other way around

[palmeirense_]

did you look at the attached file?

go to the data model and look at the tables

you cant add a pivot to the data model
a pivot is based on a table, not the other way around

Yes i saw your file.
What I am saying is my actual data (not the mockup data) is in a pivot table, connected live to a Power BI dataset.
So I can't convert it to a table in this instance

[Joske920]

Hi,

I can find any way to connect to the pivot data myself

a solution could be to copy the pivot and past to a table as values and then get the data as I have given in the answer.

sorry to disappoint.