how to use slicer from a different table in powerpivot

Question

I have a weird powerpivot that I'm hoping you experts could help...
The first table has agreementID and associating department, and the second table has agreementID and associating revenue (see below), I've also created a join between table1&table2 using agreementID.
Currently I have a pivot table that shows the agreement ID and total revenue (all from the second table). I created a department slicer from the first table. I was hoping this slicer would allow users to filter on the agreement based on the associating department (i.e. if "finance" is selected, only agreement 123/789 will show up).Right now, regardless of what I select in the slicer, all three agreements show up in the pivot.

Metric:=CALCULATE(SUM('Table2'[Revenue])

What am I doing wrong and how could I fix this issue? would appreciate any help/support you can provide!

thank you,

Table 1

AgreementID | Department
========================
123         | Sales
123         | Finance
123         | Consulting
123         | Marketing
456         | Sales
456         | Consulting
456         | Marketing
789         | Sales
789         | Finance
789         | Marketing

Table 2

AgreementID | Revenue
=====================
123         | 900000
456         | 200000
789         | 400000

Answer 1

Metric:=
CALCULATE(
    SUM(Table2[Revenue])
    ,Table1
)

You've got an abnormal table structure wherein your fact exists on the 1 side of a 1-many relationship. In DAX, you can force context to flow "uphill" from the many to the 1 by using a table reference in CALCULATE() from the many side of the relationship.

If using Power BI Desktop you can set a filter to be bi-directional and avoid having to write measures specially to deal with this situation. This will also be present in Tabular 2016, and therefore in Excel 2016.

You should also really give your tables better, more descriptive names than Table1 and Table2.

Below is an image showing everything in my model: