How to append calculated columns in a new table using DAX?

[AdityaG]

Hi all, I created different tables to calculate as the algorithms were different.

First Team	Calculated column A
fafsdf	26
sdfds	29
eerere	16
ojoj	27

Second Team	Calculated column B
wqe	17
fdfs	23
nntht	24

Third Team	Calculated column C
efew	18
nhnhhf	12
piou	14

I have created a separate table, where the team columns are appended using power query:

Overall team

fafsdf

sdfds

eerere

ojoj

wqe

fdfs

nntht

efew

nhnhhf

piou

Want a calculated column as:

Overall team	Final Column
fafsdf	26
sdfds	29
eerere	16
ojoj	27
wqe	17
fdfs	23
nntht	24
efew	18
nhnhhf	12
piou	14

If there's a way to add a column and append all three columns let me know.

[Greg_Deckler · Accepted solution]

@AdityaG Try:

Final Column = 
  VAR __Team = [Overall team]
  VAR __Value1 = MAXX( FILTER( 'Team 1 Table', [First Team] = __Team ), [Calculated column A] )
  VAR __Value2 = MAXX( FILTER( 'Team 2 Table', [Second Team] = __Team ), [Calculated column B] )
  VAR __Value3 = MAXX( FILTER( 'Team 3 Table', [Third Team] = __Team ), [Calculated column C] )
  VAR __Result = MAX( MAX( __Value1, __Value2 ), __Value3 )
RETURN
  __Result

[AdityaG]

Thanks @Greg_Deckler for the solution. I have some queries regarding the solution though.

I am confused with the last line, var _result = max(max(...,...),...)

How would this statement change if there were more than 3 tables, let's say 5 tables?

Would it be like this: var _result = max(max(max(max(....,....),...),...),...) ??

Also if one of my tables contains calculated measure instead of calculated column, how would the DAX change? Let's say Table 1 has calculated measure, while table 2 and 3 have calculated columns. Let me know if any solution is possible in this case.

[Greg_Deckler]

@AdityaG That's correct with the 5 versus 3 tables although you might be better served at that point doing something like:

MAXX( { __Value1, __Value2, __Value3, __Value4, __Value5 }, [Value])

It's generally a super bad idea to mix measures and calculated columns. Calculated columns do not recalculate dynamically like measures, only during data refresh so it's general bad to mix the two.