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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors