topic Calculate final overall value for new column based on my custom order of another calculated column in DAX Commands and Tips

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

RYRY — Tue, 02 Jan 2024 01:16:33 GMT

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

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

Fowmy — Tue, 02 Jan 2024 06:54:55 GMT

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

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

Anonymous — Tue, 02 Jan 2024 08:21:13 GMT

Hi RYRY,

Please follow these steps:

Use CALCULATE() to group by Customer_ID and then take the minimum value.
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!

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

RYRY — Tue, 02 Jan 2024 22:57:17 GMT

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

Cheers