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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
QueenTink
Frequent Visitor

CONCATONATEX combining fields from two tables.

Hello!

I am needing to combine two fields, each from different tables in my dataset. Example of the data is

Table1

productkey  productseries

12345             XYZ23

 

Table2

productkey productcode

12345           001-002-003

What I need is a column in Table1 with productseries &"-"& productcode (XYZ23-001-002-003)

 

I've tried a variety of different functions, but I'm not having any success. My latest attempt is below.

NewField= CONCATENATEX(Table1,CROSSFILTER(Table1[productkey],Table2[productkey],Both),Table1[productseries] &"-"& Table2[productcode])

 

Thank you

4 REPLIES 4
m3tr01d
Continued Contributor
Continued Contributor

@QueenTink Do you have a relationship between your two tables?
Is it Many to one?

Greg_Deckler
Super User
Super User

@QueenTink How about:

Column = [productseries] & "-" & RELATED('Table2'[productcode])

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler I get an error saying it cannot find name. The only fields that seem to be available for this are other calculated fields in the table.

@QueenTink Are you trying to create a column or a measure? The post said column. If you need a measure it will be like:

Column = MAX([productseries]) & "-" & MAX('Table2'[productcode])

This assumes an appropriate relationship between the tables.

 


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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.