Needing help with a MAX and/or RANK order to provide a specific status

[CA8172]

Good Day All,

I have one table that has customer order information and the status of the order, as well as, another table with a ranking order.  I'm trying to condense the order number to provide the current status order based on a rank.

Below is my customer information table (Order No, Trans ID, Status):

ORDER NO	TRANS ID	STATUS
147852	001	In Progress
147852	002	Packaged
147852	003	Sent
201456	001	In Progress
201456	002	Packaged
369852	001	Sent
369852	002	Sent
369852	003	Sent
410236	001	Packaged
410236	002	Sent

Here is the ranking table (Status, Rank):

STATUS	RANK
In Progress	1
Packaged	2
Sent	3

I'm trying to come up with a column that gives me one order number to the highest rank so that my table would show the below, so we know where the actual order truly stands (Order No, Status -Formula-).

ORDER NO	STATUS (Formula)
147852	In Progress
201456	In Progress
369852	Sent
410236	Packaged

Any assistance would greatly be appreciated.  Thanks!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[mahoneypat · Accepted solution]

There are probably better ways to do this, but this one works.  It assumes you have a relationship between your Orders and Rank table (that's what I called them, so replace as necessary) on the Status column.  I noted your Orders table already has a ranks column (001, 002, etc.), but I assume that is just how you made the sample data so did not use that column.

If you can use the Trans ID column, you can just use this measure

Latest Status 2 = var mintransid = MIN(Orders[TRANS ID])
return CALCULATE(MIN(Orders[STATUS]), Orders[TRANS ID] = mintransid)

If not, here is one that uses both of your tables

Latest Status =
VAR summarytable =
    ADDCOLUMNS (
        SUMMARIZE ( Orders, Orders[ORDER NO], Orders[STATUS] ),
        "@StatusRank", CALCULATE ( MIN ( 'Rank'[RANK] ) )
    )
VAR toprank =
    TOPN ( 1, summarytable, [@StatusRank], ASC )
RETURN
    MINX ( toprank, Orders[STATUS] )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[CA8172]

Thank you...preliminary finding has this working for me.