Grouping column values and setting a flag based on another column's value?

[bballjoe12]

I have a large set of data that contains ticket numbers and the history of the Groups the ticket numbers were assigned to. The data may consist of 4 rows for, example, for ticket # 126748, because it was assigned to 4 different teams.

I'm trying to figure out a measure that will set the flag to '1' if the ticket numbers assigned group was ever 'Desktop Support.' If the ticket number's assigned group did include 'Desktop Support' then I want to set the flag on every row of that ticket # to '1'.  Here is a screenshot of what I am trying to achieve:

I've searched and tried many different DAX combinations and have been very unsuccessful.  All of my data is on the same table.  Does anyone know a DAX measure that would help me achieve this?

[CNENFRNL]

Excel worksheet formula is already enough

[ValtteriN · Accepted solution]

Hi,

Here is one way to do this:

Measure 15 = if(CALCULATE(CONTAINS('Temperature (2)','Temperature (2)'[Temperature],"Warm"),ALLEXCEPT('Temperature (2)','Temperature (2)'[GRP])),1,0)

This measure checks if a group contain "Warm" and will return 1 to all rows where this is the case.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[bballjoe12]

@ValtteriN 

Can I use a wildcard within the syntax to get groups that start with DS? Here is the syntax I am using to get one group:

DS Assigned = if(CALCULATE(CONTAINS('Incident History','Incident History'[Assigned Group],"DS_Unity"),ALLEXCEPT('Incident History','Incident History'[Incident #])),1,0)

I tried this but had no luck:

DS Assigned = if(CALCULATE(CONTAINS('Incident History','Incident History'[Assigned Group],"DS_*"),ALLEXCEPT('Incident History','Incident History'[Incident #])),1,0)

How would I use a wildcard within the syntax to search any group that starts with 'DS'?

[bballjoe12]

@ValtteriN 

How would I change this syntax to use it in creating a new table rather than just a measure?

DS Assigned = if(CALCULATE(CONTAINS('Incident History','Incident History'[Assigned Group],"DS_Unity"),ALLEXCEPT('Incident History','Incident History'[Incident #])),1,0)

[ValtteriN]

Have a look at CONTAINSSTRING:
e.g.
wildcard = 

CONTAINSSTRING(max(Temperature[Temperature]),"Warm")

non-wild =

CONTAINS(Temperature,Temperature[Temperature],"Warm")

[bballjoe12]

You are a genius! This works exactly as I need.  Thank you so much!