Evaluation based on several conditions

[Peter_2020]

Hi all, 

I would like to ask you for help with DAX formula for this case:

I have the table with order number, operation number and code:

ORDER	OPERATION	CODE
111	0100	01
111	0100	02
111	0200	01
111	0200	02
222	0110	01
222	0110	02
222	0210	01

Each operation have 01 code which means that operation started and 02 code when the operation was closed. And what I want to do is identify which order with specific operation is open or closed.

So I need to create new measure/column where will be defined following rules:

 - if operation number containts "02" code than it is closed

 - if operation number doesn´t containts code "02" than it is open

Here is desired output:

ORDER	OPERATION	CODE	STATUS
111	0100	01	closed
111	0100	02	closed
111	0200	01	closed
111	0200	02	closed
222	0110	01	closed
222	0110	02	closed
222	0210	01	open

All orders and operations contains "02" code therefore are closed only order 222 with operation 0210 doesn´t have "02" code so therefore is open...

Any idea how to define the formula?

Thanks for your help.

P. 

[Fowmy · Accepted solution]

@Peter_2020 

You can add the following column to your table.

Status = 
VAR __OR = Table1[ORDER]
VAR __OP = Table1[OPERATION]
VAR __CODE = 
    CALCULATETABLE(
        VALUES(Table1[CODE]),
        Table1[ORDER] = __OR,
        Table1[OPERATION] = __OP,
        REMOVEFILTERS(Table1)
    )
VAR __RESULT = 
    IF(
        "02" IN __CODE,
        "CLOSED",
        "OPEN"
    )
RETURN
    __RESULT

If you like a measure, this should work:

M1 = 
VAR __CODE =
        VALUES(Table1[CODE]) 
VAR __RESULT = 
    IF(
        "02" IN __CODE,
        "CLOSED",
        "OPEN"
    )
RETURN
IF(  
    HASONEVALUE(Table1[OPERATION]),
    __RESULT
)

[Peter_2020]

@Fowmy Thank you very much. It works!