Returned "Status" value based on largest number in index column

[Jays_D]

Hi All,

New to PBI and could really use some help working this one out.

I have a table which has multiple rows, which can be the same account repeated a few times. A column which has a Status value and an index column (which has a value assigned 1-21 for each Status in the Status value column). Looks something like this:

Account	Status	Index	Last Status
ABC123	Waiting	1
ABC123	Commenced	2
DEF456	Waiting	1
GHI789	Complete	3

What I am trying to do is have a new column (Last Status) which will return the "Status" for an account based on the largest Indexed number (lets use ABC123 as the example). Using the sample above, the result would be "Commenced".

Apologies, I hope I am making sense. Any guidance would be greatly appreciated.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

It is for creating a calculated column.

INDEX function (DAX) - DAX | Microsoft Learn

Last status CC =
VAR _t =
    INDEX (
        1,
        Data,
        ORDERBY ( Data[Index], DESC ),
        ,
        PARTITIONBY ( Data[Account] ),
        MATCHBY ( Data[Index] )
    )
VAR _latestindex =
    SUMMARIZE ( _t, Data[Index] )
RETURN
    IF ( Data[Index] = _latestindex, SUMMARIZE ( _t, Data[Status] ) )