Filter on columns from two different tables

[Reddyp]

Hi, I need to calculate SUM of (Sheet1)sales where (Sheet1)YEAR is 2020 and LOCTION is (Sheet2)AUCKLAND

the "location" is present on the other table (Sheet 2)

[Samarth_18]

Hi @Reddyp ,

Please try this:-

CALCULATE (
    SUM ( Sheet1[sales] ),
    FILTER ( Sheet1, Sheet1[Year] = 2020,
    FILTER ( Sheet2, Sheet2[Location] = "Auckland" )
)

Thanks,

Samarth

[danextian]

Hi @Reddyp ,

Assuming there is is a one-to-many realtionship between Sheet2[LocationColumn] and Sheet1[LocationColumn],  this measure should work:

My Measure =
CALCULATE (
    SUM ( Sheet1[Sales] ),
    FILTER ( Sheet1, Sheet1[Year] = 2020 && Sheet2[Location] = "Auckland" )
)

[Reddyp]

@danextian  its not   one-to-many , Its many - to -One realtionship between Sheet2[LocationColumn] and Sheet1[LocationColumn]

and as you said this didn't work, please help me out

[v-chenwuz-msft]

Hi @Reddyp, 

Have your problem been solved? If so, please share your solution so that if someone else has the same problem as you, he can get the answer quickly.

If not, please try to change the relationship between sheet1 and sheet2 and set the cross filter direction as both.

Then use the code provided by @Samarth_18 . 

CALCULATE (
SUM ( Sheet1[sales] ),
FILTER ( Sheet1, Sheet1[Year] = 2020,
FILTER ( Sheet2, Sheet2[Location] = "Auckland" )
)
 

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian · Accepted solution]

I missed something. Please try this:

2020 Auckland = 
CALCULATE (
    SUM ( Sheet1[Value] ),
    FILTER ( Sheet1, Sheet1[Year] = 2020 && RELATED ( Sheet2[Location] ) = "Auckland" )
)

You may refer to this PBIX with a mock up data: https://drive.google.com/file/d/16v71ZONREaPauBzUTYvMIScFu8IwCc9u/view?usp=sharing 

If this doesn't work, please share a pbix with sanitized data.

Here's how you can get your questions answered quickly - https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/2547186#M9020...