Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

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


Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

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


Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

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.



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.