Assign unique identifier based on multiple attributes

[Anonymous]

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. 

[Ashish_Mathur]

Hi,

You may refer to my solution here.

Hope this helps.

[Phil_Seamark]

Hi @Ashish_Mathur

This one had already been answered. 

[Phil_Seamark · Accepted solution]

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],","))

[Anonymous]

Thank you Phil, this has helped open me up to the use of Variables, something I hadn't been familiar with previously. This is a huge help - and I'm finding I can do much more with it now!