topic Re: Display Common values between multiple tables and display them in a new table in DAX Commands and Tips

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

DeeBali — Tue, 12 Mar 2024 20:57:25 GMT

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.

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

talespin — Wed, 13 Mar 2024 07:18:11 GMT

hi DeeBali

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

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

FreemanZ — Wed, 13 Mar 2024 07:28:04 GMT

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.

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

DeeBali — Wed, 13 Mar 2024 19:34:38 GMT

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?

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

DeeBali — Wed, 13 Mar 2024 19:35:09 GMT

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

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

FreemanZ — Thu, 14 Mar 2024 03:56:48 GMT

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.