Multiple Filters across different workbooks

[lem5072]

Hello, 

I am trying to apply 2 filters across 2 different fields in 2 different workbooks. I cannot merge the files as they deal with seprerate data and that would not work. I need to filter by Office AND by YEAR. However when i create the relationships I cannot link these tables on multiple fields. When i then create a table to link the Office to Workbook 1 then Year from Workbook 1 to Workbook 2 this also doesn't work. I hav also tried the formula 

Year Sep Count = calculate(count(Workbook2[Count]), filter('Workbook2','Workbook2'[Year] in values(Workbook1l[Year])))

 

Workbook 1		Workbook 2
Count	Office	Year	Age			Office	Year	Leave Taken
13987	Front Office	2012	43			Front Office	2012	Y
13989	Front Office	2014	67			Front Office	2014	N
13990	HR	2015	33			HR	2015	Y
13991	Front Office	2014	24			Front Office	2014	N
13992	IT	2019	65			IT	2019	Y
13993	IT	2020	23			IT	2020	Y
13994	HR	2014	65			HR	2014	N
13995	Front Office	2016	19			Front Office	2016	Y

My Dashboard has both the Office Filter and the Year filter, so when you select either I want both tables to filter appropriatley. Creating 2 seperate lookup tables for the Office and Year also cause a circular relationship 

[lem5072]

Thank you for the help!

[amitchandak · Accepted solution]

@lem5072 , You can create common office and year (if needed date table) dimension and join with both tables and use those dimensions as slicer and visual along with measure from two tables

new tables 

Office=

distinct(union(distinct('Table'1[Office]),distinct('Table'2[Office])))

year =

distinct(union(distinct('Table'1[year ]),distinct('Table'2[year ])))

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19