Forum Discussion
Assign unique identifier based on multiple attributes
Hello,
I have a set of data that describes case work coming into a support organization, including who owns it during different points over the case lifecycle. What I need to do is describe each case as having been owned by a group, i.e. 'Red', 'Blue' or 'Red&Blue', based on the Team that shows up in the ownership history. I have a separate table that maps the Teams to their group, something like the following:
Team A = Red
Team B = Blue
(In reality, I have many more Team combinations, but just trying to keep it simple to get the logic)
Tables:
Case[Case]
User[Team]
History[Time]
Case Team Time
123 A 11:23
123 B 12:34
123 A 12:54
456 B 1:50
456 B 1:56
789 A 10:21
234 A 7:45
234 A 7:52
234 B 7:59
I'd like to produce a table that includes the following:
Case Group
123 Red&Blue
456 Blue
789 Red
234 Red&Blue
I intend to use the output against other attributes within the Case table, so whether it actually needs to be a table or a measure may be immaterial(?). Any help is appreciated - thank you in advance.
Hi Anonymous
This calculated table might be close.
Table = VAR DistinctValues = SUMMARIZECOLUMNS('Table1'[Case],'Table1'[Team]) VAR Final = SUMMARIZECOLUMNS('Table1'[Case]) RETURN ADDCOLUMNS( Final , "Group" , CONCATENATEX( FILTER(DistinctValues,[Case] = EARLIER('Table1'[Case])), [Team],","))
4 Replies
Hi Anonymous
This calculated table might be close.
Table = VAR DistinctValues = SUMMARIZECOLUMNS('Table1'[Case],'Table1'[Team]) VAR Final = SUMMARIZECOLUMNS('Table1'[Case]) RETURN ADDCOLUMNS( Final , "Group" , CONCATENATEX( FILTER(DistinctValues,[Case] = EARLIER('Table1'[Case])), [Team],","))
