cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jl48 Regular Visitor
Regular Visitor

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: Assign unique identifier based on multiple attributes

Hi @jl48

 

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

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

4 REPLIES 4
Phil_Seamark Super Contributor
Super Contributor

Re: Assign unique identifier based on multiple attributes

Hi @jl48

 

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

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

jl48 Regular Visitor
Regular Visitor

Re: Assign unique identifier based on multiple attributes

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!

Super User
Super User

Re: Assign unique identifier based on multiple attributes

Hi,

 

You may refer to my solution here.

 

Hope this helps.

 

Untitled.png

Phil_Seamark Super Contributor
Super Contributor

Re: Assign unique identifier based on multiple attributes

Hi @Ashish_Mathur

 

This one had already been answered. 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 105 members 1,540 guests
Please welcome our newest community members: