Filtering sales table with all article sales only from stores with specific article selection

[javierzamb]

Dear Power BI community,

I have a Sales table with columns of Stores, Article, Month and Sales, I also have the corresponding dimension tables for Stores and Articles. I would like to measure the performance of specific articles, but only considering the sales from the stores that actually sells those articles. So I need to keep the sales from all articles, from that subset of stores.

Here is an example...

My data (very simplified) looks like this:

Store	Article	Month	Sales
Downtown	Soda	January	10
Downtown	Chips	January	25
Downtown	Candy	January	32
Downtown	Milk	January	23
South	Chips	January	65
South	Candy	January	27
South	Milk	January	28
South	Cookies	January	38
North	Soda	January	2
North	Candy	January	40
North	Milk	January	13
North	Juice	January	11
West	Soda	January	23
West	Chips	January	34
West	Candy	January	45

Let's say I choose in a slicer the Articles "Juice" and "Cookies". Those two articles have sales in stores "South" and "North", so I would expect to get a table like this:

Store	Article	Month	Sales
South	Chips	January	65
South	Candy	January	27
South	Milk	January	28
South	Cookies	January	38
North	Soda	January	2
North	Candy	January	40
North	Milk	January	13
North	Juice	January	11

The Article selection must be dynamic to see the sales f

How can I do this in Power BI? I've been trying for hours trying to create new tables, but I haven't been successful.

Thank you so much in advance for the help.

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[javierzamb]

My initial approach was something "similar" calculating a table, but this one actually works! I still need to test it when using huge tables, but looks promising. Thanks!

[Daniel_PowerBI · Accepted solution]

Hi javierzamb,

You need to create a seperate dimension with both Stores and articles, connect it with store dimension (with both sided relationship) in order to do so

Store-Article Dimension:

and the relationships:

and then you can add a slicer with the article column of the Store-Article Dimension to get your result:

[javierzamb]

Didn't think about this approach. I just tested it with the real databases and works great. Thanks!