Forum Discussion

jimpatel's avatar
jimpatel
Icon for Post Patron rankPost Patron
2 years ago
Solved

Dax if formula

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's avatar
    FreemanZ
    2 years ago

    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:

     

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
    jimpatel
    Icon for Post Patron rankPost Patron
    2 years ago

    Perfect 🙂

     

    This is what exactly i need.

     

    You are star

     

    Thanks a lot