Forum Discussion
Helper IHow to append calculated columns in a new table using DAX?
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.
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
3 Replies
- Greg_Deckler
Community Champion
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
AdityaGThanks 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.