Display Common values between multiple tables and display them in a new table

[DeeBali]

Hello Fabric Community,

I need some assistance with DAX for my project. I have multiple month tables labeled as "01 2024" , "02 2024" etc.. and each table has a column called "Email Address". I need to find a way to compare this coulmn in each table and display Email Address that show up in every table as a new column "3 Months" in a new table "2024". For example:
 01 2024                  02 2024                  03 2024                         

Email Address         Email Address         Email Address

abc@gmail.com       def@gmail.com       abc@gmail.com
def@gmail.com      ghi@gmail.com        ghi@gmail.com
ghi@gmail.com 

Result:
2024

3 Months

ghi@gmail.com    

Please assist how I can make a join between multiple tables to only show common values in a new coulmn. Thank you. 

[FreemanZ]

not sure how your situation looks like. it might make sense to append your monthly table and add a mm-yyyy column, that would make your case much easier. 

[DeeBali]

Okay, by appending will it add all the months data together in one single query? and from there I should use slicers or filters I assume? 

[FreemanZ · Accepted solution]

yes, indeed. 

1) Load all the monthly tables to Power Query

2) add a mm-yyyy column accordingly in each monthly table

3) append all monthly tables

(all three steps in Power Query)

then do the analysis with Power BI desktop or excel. 

[talespin]

hi @DeeBali 

Joins are expensive, I would reconned you do it in Power Query. How big are your tables?

[DeeBali]

each month has about 5000 rows worth of data. Thank you.