Multiple Filters across different workbooks

Question

Hello,&nbsp;&nbsp;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&nbsp;&nbsp;&nbsp;Year Sep Count = calculate(count(Workbook2[Count]), filter('Workbook2','Workbook2'[Year] in values(Workbook1l[Year])))&nbsp; Workbook 1&nbsp;Workbook 2CountOfficeYearAge&nbsp;&nbsp;OfficeYearLeave Taken13987Front Office201243&nbsp;&nbsp;Front Office2012Y13989Front Office201467&nbsp;&nbsp;Front Office2014N13990HR201533&nbsp;&nbsp;HR2015Y13991Front Office201424&nbsp;&nbsp;Front Office2014N13992IT201965&nbsp;&nbsp;IT2019Y13993IT202023&nbsp;&nbsp;IT2020Y13994HR201465&nbsp;&nbsp;HR2014N13995Front Office201619&nbsp;&nbsp;Front Office2016Y&nbsp;&nbsp;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&nbsp;&nbsp;

amitchandak · Accepted Answer

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 
&nbsp;
new tables&nbsp;
Office= 
distinct(union(distinct('Table'1[Office]),distinct('Table'2[Office])))
&nbsp;
year =
distinct(union(distinct('Table'1[year ]),distinct('Table'2[year ])))
&nbsp;
Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&amp;list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&amp;index=19

Forum Discussion

Multiple Filters across different workbooks

2 Replies

Related content

How to load multiple excel workbooks each with multiple worksheets?

Multiple workbooks

Power Query Merge Multiple workbooks in a Folder

Date difference with multiple criteria

Workbook into Lakehouse

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