The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.
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 | |
13 | |
8 | |
6 | |
6 |
User | Count |
---|---|
24 | |
20 | |
13 | |
9 | |
7 |