topic Re: Combine two columns...(Not Merge) in DAX Commands and Tips

Combine two columns...(Not Merge)

amani1980 — Sat, 02 Nov 2024 21:08:12 GMT

Hi,

I'm looking to combine data from two columns from different tables. Not merge. Each table has two columns country and event and I want to create a new table that contains both tables as shown below. I know I can do this by Append Query as New in Power Query. However, this slows the model a lot. I have shown only two columns of each tables whereas in reality there are more than 30-40 columns and 1000s of rows in each table. So append queries and delete the remaining columns is very tedious and slwos the data refresh. Is there any way I can pull only these two columns from the two tables directly and create a combined new table witout losing the relation between Country and Ticket? Model here....

Thanks in advance,

-AM

Re: Combine two columns...(Not Merge)

Irwan — Sat, 02 Nov 2024 23:22:05 GMT

hello amani1980

i might be misunderstood but perhaps what you are looking for is UNION().

here is a simple example:
- Table 1

- Table 2

- New Table with UNION() DAX

Hope this will help.

Thank you.

Re: Combine two columns...(Not Merge)

amani1980 — Sat, 02 Nov 2024 23:51:14 GMT

Hi,

Thanks for replying!

I want to do something similar not quite the same - I don't want to combine all columns from both tables. Just the Country and Ticket columns from the 30-40 colunms there is in each one of the tables. I showed just the two columns in the screenshot for sake of simplicity.

Re: Combine two columns...(Not Merge)

FreemanZ — Sat, 02 Nov 2024 23:59:01 GMT

hi amani1980 ,

try to write a calculated table like:

table3=

DISTINCT(

UNION(

SUMMARIZE(

table1,

table1[country],

table1[ticket]

SUMMARIZE(

table2,

table2[country],

table2[ticket]

)