cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AdityaG
Helper I
Helper I

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

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

 

First TeamCalculated column A
fafsdf26
sdfds29
eerere16
ojoj27

 

Second TeamCalculated column B
wqe17
fdfs23
nntht24

 

Third TeamCalculated column C
efew18
nhnhhf12
piou14

 

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 teamFinal Column
fafsdf26
sdfds29
eerere16
ojoj27
wqe17
fdfs23
nntht24
efew18
nhnhhf12
piou14

 

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

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors