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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.

Reply
PGY
Frequent Visitor

CONCATENATEX - skip the next value if it's the same with the previous one

Dear expert,

 

I have a ticket support table with date/time, team, specialist, status fields. I would like to track the ticket transfer between teams.

Since, the ticket can be transferred to a different specialist within a team, it causes the team names to be repeated when I use DAX: concatenatex(table, team, " - ", date, asc). 

 

Is there an alternative that allows concatenating but should the next value is the same with the previous one, it will skip until it finds the next team with a different name?

Note: the ticket can be transferred back to a certain team as well. E.g.: Team A - Team B - Team C - Team C - Team A - Team D - Team D. In this case, the expected result will be Team A - Team B - Team C - Team A - Team D.

 

Thanks in advance.

1 ACCEPTED SOLUTION

Power Query:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJR0lEKSU3MVXBUitWJVjJCE3UCixqjiTqDRU2wippiNdcMTdQFLGqOKRoLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Team = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Team", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Team"}, {{"Min Date", each List.Min([Date]), type nullable date}},GroupKind.Local)
in
    Text.Combine(#"Grouped Rows"[Team]," - ")

 

 

DAX:

Concat = 
var a = ADDCOLUMNS('Table',"same",[Team]=selectcolumns(OFFSET(-1,all('Table'),MATCHBY('Table'[Date])),[Team]))
return CONCATENATEX(filter(a,not [same]),[Team]," - ")

lbendlin_0-1719707941049.png

 

View solution in original post

3 REPLIES 3
PGY
Frequent Visitor

It can be in Power Query as well

Power Query:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJR0lEKSU3MVXBUitWJVjJCE3UCixqjiTqDRU2wippiNdcMTdQFLGqOKRoLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Team = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Team", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Team"}, {{"Min Date", each List.Min([Date]), type nullable date}},GroupKind.Local)
in
    Text.Combine(#"Grouped Rows"[Team]," - ")

 

 

DAX:

Concat = 
var a = ADDCOLUMNS('Table',"same",[Team]=selectcolumns(OFFSET(-1,all('Table'),MATCHBY('Table'[Date])),[Team]))
return CONCATENATEX(filter(a,not [same]),[Team]," - ")

lbendlin_0-1719707941049.png

 

lbendlin
Super User
Super User

Does this have to be in DAX or could it be done in Power Query?

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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