Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Peter_2020
Helper III
Helper III

Evaluation based on several conditions

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:

 

ORDEROPERATIONCODE
111010001
111010002
111020001
111020002
222011001
222011002
222021001

 

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:

ORDEROPERATIONCODESTATUS
111010001closed
111010002closed
111020001closed
111020002closed
222011001closed
222011002closed
222021001open

 

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. 

 

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@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

Fowmy_0-1632034913910.png


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
)

Fowmy_1-1632035149049.png

 







Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

2 REPLIES 2
Peter_2020
Helper III
Helper III

@Fowmy Thank you very much. It works!

Fowmy
Super User
Super User

@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

Fowmy_0-1632034913910.png


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
)

Fowmy_1-1632035149049.png

 







Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors