topic How to append calculated columns in a new table using DAX? in DAX Commands and Tips

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

AdityaG — Sat, 27 May 2023 13:25:14 GMT

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.

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

Greg_Deckler — Sat, 27 May 2023 15:49:03 GMT

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

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

AdityaG — Sun, 28 May 2023 07:29:45 GMT

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.

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

Greg_Deckler — Sun, 28 May 2023 13:38:26 GMT

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.