Calculate final overall value for new column based on my custom order of another calculated column

[RYRY]

Hi 

I am trying to work out how to get a final value based on a hierarchy/custom order (I have added a number for simplicity alpha-numeric ordering) from another column. Some records may only have one row per customer_id so they aren't an issue, but some other customer records may have multiple rows. Each of those row can have a different outcome value. 

I have used the RANKX function unsuccessfully at this stage but I may be using the incorrect terminolgy here. 

Here is what I would like to occur (thanks in advance!)

Customer_ID	Individual_State (NB this column is calculated)	**Expected column** Overall_State
abc	1. Shipped	SHIPPED
cde	1. Shipped	SHIPPED
cde	2. Paid	SHIPPED
efg	3. Ordered	ORDERED
efg	4. Checked	ORDERED
efg	4. Checked	ORDERED
hij	5. Confirmed	PAID
hij	2. Paid	PAID

[v-huijiey-msft · Accepted solution]

Hi @RYRY,

Please follow these steps:

1. Use CALCULATE() to group by Customer_ID and then take the minimum value.
2. Use RIGHT(), based on "." Take the right half of the separation and then turn capitalization to get the results you want.

The DAX expression for the calculated column is as follows:

Overall_State = 
VAR _last_value = CALCULATE(MIN('Table'[Individual_State]),ALLEXCEPT('Table','Table'[Customer_ID]))
VAR _result = RIGHT(_last_value,LEN(_last_value)-SEARCH(".",'Table'[Individual_State]))
RETURN
UPPER(_result)

Result:

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[RYRY]

Awesome! So simple but I need to learn the 'ALLEXCEPT' function a bit more.

Cheers

[Fowmy]

@RYRY 

First, create atable to store the Status and Sequence Order

Add the following calculated column:

Overall State = 
VAR __Order = 
    MINX(
    ADDCOLUMNS(
        CALCULATETABLE( VALUES( Table02[Individual_State] ) , ALLEXCEPT( Table02 , Table02[Customer_ID] )),
        "Order" , LOOKUPVALUE( OrderStatus[Order] , OrderStatus[Status], Table02[Individual_State] )
    ),
    [Order]
    )
VAR __Result =  LOOKUPVALUE( OrderStatus[Status] , OrderStatus[Order], __Order )
RETURN
    __Result