Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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.
Solved! Go to 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] ) )
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] ) )
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
56 | |
56 | |
38 | |
29 |
User | Count |
---|---|
75 | |
62 | |
45 | |
40 | |
40 |