topic Re: Grouping column values and setting a flag based on another column's value? in DAX Commands and Tips

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

bballjoe12 — Wed, 02 Feb 2022 06:38:44 GMT

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?

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

ValtteriN — Wed, 02 Feb 2022 06:48:28 GMT

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/

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

CNENFRNL — Wed, 02 Feb 2022 10:45:31 GMT

Excel worksheet formula is already enough

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

bballjoe12 — Wed, 02 Feb 2022 15:15:24 GMT

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

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

bballjoe12 — Wed, 02 Feb 2022 16:33:28 GMT

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'?

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

ValtteriN — Wed, 02 Feb 2022 16:43:48 GMT

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

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

non-wild =

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

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

bballjoe12 — Thu, 10 Feb 2022 04:49:28 GMT

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)