Need help

[Anonymous]

I need help with writing a dax to return a true or false if a value is found in the column after it was filtered by job number 

eg: the value to be looked for is red 

It's supposed to return true for job number 1 since red is in the second row. For job 2 and 3 it returns false because red is nowhere in the colour column for these job numebrs

job number	colour	true/false
1	blue	true
1	red	true
1	orange	true
2	yellow	false
2	black	false
3	white	false
3	pink	false
3	brown	false

[Anonymous]

Thanks @rs 

What if I want to add more than one value to look for ? 

example: red and gray 

[rsbin · Accepted solution]

@Anonymous ,

There are a few different ways this could be done.  But I would simply change the IF to a SWITCH function:

True/False2 = 
VAR _Currentjobnumber = [job number]
VAR _Colour = SUMMARIZE( FILTER( Colour, Colour[job number] = _Currentjobnumber), Colour[colour])
VAR _Result = SWITCH(
                  TRUE(),
                  "red" in _Colour, "True",
                  "gray" in _Colour, "True",
                  "False" )

RETURN
    _Result

The SWITCH is similar to a nested IF, allows for multiple conditions but is much cleaner and easier to read.

Now you can add as many colours as you need.

Regards,

[rsbin]

@Anonymous .

Please try the following as a Calculated Column:

True/False = 
VAR _Currentjobnumber = [job number]
VAR _Colour = SUMMARIZE( FILTER( Colour, Colour[job number] = _Currentjobnumber), Colour[colour])

RETURN
   IF( "red" IN _Colour, "True", "False" )

job number	colour	True/False
1	blue	True
1	red	True
1	orange	True
2	yellow	False
2	black	False
3	white	False
3	pink	False
3	brown	False

Regards,