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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Jays_D
Advocate II
Advocate II

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

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:

AccountStatusIndexLast Status
ABC123Waiting1 
ABC123Commenced2 
DEF456Waiting1 
GHI789Complete3 

 

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.

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_0-1707457644270.png

 

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] ) )

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_0-1707457644270.png

 

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] ) )

 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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