cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Scotto13
Helper III
Helper III

Concatenate field in PBI

I have data looking like this:

Ticket   Tech
233450 qmi3
233450 top3t3
233716 hql8qoh
233893 wqd
234081 app3on
234179 momqt3
234179 hqw

 

I would like to create a DAX field that concatenates the Tech field so the data looks like this:

Ticket   Tech
233450 qmi3,top3t3
233716 hql8qoh
233893 wqd
234081 app3on
234179 momqt3,hqw

All help is much apprecated!

7 REPLIES 7

Well, this can be done in Power Query or DAX. Which is best depends on what you are wanting to do with it. In DAX, you could write a measure like this. 

CONCATENATEX(TableName,TableName[Tech],",")

 

place the ticket column in a matrix on rows and the measure above in the values section



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Matt, I'm using Direct Query and need to buils the field in DAX,
I tried thie formula you gave me and it just places a comma after the existing field.
I need to concatenate the data from other rows when the Ticket ID is the same.

You need to change my formula TableName and TableName[ColumnName] to match your model



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Yes, I changed the formula to this: TechConcat = CONCATENATEX(Installs,Installs[TECH],",")
I get this error: 

Error Message:
ORA-01704: string literal too long. The exception was raised by the IDbCommand interface.

 

my guess is that this is the total row.  Try this

 

= IF(ISFILTERED(Installs[TECH]),CONCATENATEX(Installs,Installs[TECH],","))



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Ticket and Tech are columns in my Oracle db.  The query returns 4500 rows with Ticket as the id field and a Tech is on each row.  

TICKET  INSTALLER
166779  bler
166779  shan

Why is my post showing in Spanish? I can't figure how to change it back?

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors