how to create a new table that the data comes from different excel sheets and filter visuals by it

[Anonymous]

hi team,

1. I have an excel file that contains 4 sheets. 

2. I have uploaded the excel file with above 4 sheets into powerBI Desktop, and create 4 charts already.

   Now I want to filter the 4 charts by SFDC, CMN and CMID together. But I can't do this directly, the behavior now is that if i add sheet1->CMN column into 'Filters on this page' section, then only the chart based on sheet1 will be filtered, the rest 3 charts won't be affected.  If I add sheet2->CMN into filter sections as well, then there will be two CMN fitlers which is unexpected.

Help needed:

How can i filter the 4 charts in 1 page by SFDC, CMN and CMID together?? I need some data processing by DAX to build relationship for these 4 sheets, right? how to achieve it?

[Anonymous]

@tamerj1  thank you for the solution. I tried in powerBI Desktop and It works.

[tamerj1 · Accepted solution]

Hi @Anonymous 

you can create 3 lookup tables (SFDC, CMN and CMID) each containing one column of all unique values. Example:

SFDC= 

DISTINCT (UNION ( VALUES (Table1[SFDC]),VALUES (Table2[SFDC]),VALUES (Table3[SFDC]),VALUES (Table4[SFDC])))

then connect the three new tables with the original 4 tables with one to many relationships (total 12 relationships)