Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
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.
Solved! Go to 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]," - ")
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]," - ")
Does this have to be in DAX or could it be done in Power Query?
User | Count |
---|---|
15 | |
9 | |
8 | |
6 | |
5 |
User | Count |
---|---|
31 | |
18 | |
13 | |
7 | |
5 |