Forum Discussion
Grouping column values and setting a flag based on another column's value?
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?
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/
6 Replies
- ValtteriN
Community Champion
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/You are a genius! This works exactly as I need. Thank you so much!
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'?
- ValtteriN
Have a look at CONTAINSSTRING:
e.g.
wildcard =CONTAINSSTRING(max(Temperature[Temperature]),"Warm")
non-wild =CONTAINS(Temperature,Temperature[Temperature],"Warm")
CNENFRNLExcel worksheet formula is already enough
