Dax if formula

[jimpatel]

Hi,

Thanks a lot for looking at my post.

Any idea for below logic will be appreciated

I have ordernumber, operation, date and closed column in power bi. What i am looking for is, if "Closed" column is x for certain operation number then any other line in the same operation number in the same order number should be closed and rest will be open.

Raw data

Filtered version

Any help will be appreciated

Thanks a lot 

[FreemanZ · Accepted solution]

hi @jimpatel ,

not sure if i fully get you, supposing you have a data table like:

Order	Operation	Date	Closed
2	1	1/1/2024
2	3	1/2/2024	x
2	3	1/3/2024
3	1	1/4/2024
3	3	1/5/2024	x

try to add a calculated column like:

Column = 
VAR _closed =
MAXX(
    FILTER(
        data,
        data[Order]=EARLIER(data[Order])
            &&data[operation]=EARLIER(data[operation])
    ),
    data[Closed]
)
VAR _result =
IF(
    _closed = "x",
    "Closed", "Open"
)
RETURN _result

it worked like:

[jimpatel]

Perfect 🙂

This is what exactly i need.

You are star

Thanks a lot