Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to 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 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
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.
@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.
User | Count |
---|---|
21 | |
19 | |
12 | |
10 | |
9 |
User | Count |
---|---|
30 | |
25 | |
15 | |
13 | |
10 |