Calculated columns based on columns in other tales

[JamesBockett]

Hi everyone

I have 4 data tables. (T1, T2, T3 and T4).

T1 and T2 connect using column [Occ. ID]

T2 and T3 connect using column [Ves. ID] - T3 does not have column [Occ. ID]

T3 and T4 connect using column [Con. ID] - T4 does not have column [Occ. ID] or [Ves. ID]

Each have calculated columns in. I would like to know if it is possible to have a fifth table that I add calculated columns to that looks across T1, T2, T3, T4 so that all my calculated columns are in one table with nothing (or very little) else in T5?

Many thanks

James

[v-rzhou-msft]

Hi @JamesBockett 

Here I don't suggest you to create new table by dax directly. If your relationship has many side, your result will return to summarize.

I suggest you to merge your Four tables by Merge function and then create calculated column in your new table T5.

EX:

Merge T1 and T2 as a new table by [Occ. ID] and then Expand all columns you need from T2. We call New table as T5.

Then Merge T5 and T3 by  [Ves. ID] ...

You will get a new basic table and you can use this table to create calculated columns.

For reference:

Combine queries

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JamesBockett]

Hi @v-rzhou-msft 

I've done as you said and have succesfully made one table. It is 71 columns wide though. This probably isn't very good? What are your thoughts?

Many thanks

James

[amitchandak]

@JamesBockett , The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[JamesBockett]

Hi @amitchandak 

Here is an example of what I have and would like to achieve.

I hope this helps?

Thanks

James

[v-rzhou-msft]

Hi @JamesBockett 

You can try summarize, addcolumn function to create a large table by Dax. Then create new calculated columns based on your logic.

For reference:

Summarize

Addcolumns

As I replied before, if your relationship has many side, your result will return to summarize (DAX). If you want to expand your table, I still suggest you to merge your Four tables by Merge function and then create calculated column in your new table T5.

You have tried Merge, and you got a new table with 71 columns. Don't keep the useless or duplicate columns. You can optmize your table by remove columns you don't need. 

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.