Filter only one column based on the other with DAX

[Shrikant]

Hi,

i have just started using DAX practically after going through various books & blogs
I have a scenario like this
I have sales of different products by different region.
I need to build a measure in which if I select a particular region lets say region 1 which sells only Product 1, 3, 5,
All the region should show sales of only product 1,3, & 5 in their respective regions.

 

[Matt Allington]

If you write a measure sum(table[qty]) and filter on a region, then it will only show sales for products sold in that region by default.

 

[Shrikant]

Thanks Matt. But my problem is a little different.

Sorry i am unable to paste table here for more clarity
Let me explain

Suppose there are total 10 products which are sold in different regions.

For one analysis, I am benchmarking Region 1. (This benchmarking Region is fixed)
Region 1 sells only product 1,3,&5.
I need to have sales of only these 3 products in all other regions (like if Region 2 sales prodcut 1,3,5,&6 then 6 should be filtered out, similarly if region 3 sells only product 3,4,5 then measure should calculate only for product 3&5)

I have tried something like this in CALCULATE

CALCULATE(SUM(table[sales]),FILTER(DISTINCT(table[products]),table[regions]="region 1")))
but i guess filter does not work like that

 

[Matt Allington]

Please send me your email address via PM. I will send you a solution. I am planning a blog post about this next week as I think it is a good problem.