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 "Clos...
  • 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:

     

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: