Forum Discussion
Helper IHow to append calculated columns in a new table using DAX?
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
Community ChampionAdityaG 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
Helper IThanks 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.