Compare multiple rows to get the most recent version

Anonymous — Fri, 08 Jul 2022 17:53:28 GMT

Hello all,

I have a dateset containing ongoing projects for my department. I'm attempting to use Power Automate's "Run a query against a dataset" to automatical generate a list of projects that meet certain conditions. The structure of the dataset is giving me trouble and making it hard for me to write the necessary query.

Each project is assigned an ID and version number. The ID doesn't change but as the project goes through revisions, a new entry will be made in the dataset with an incremented version number. The Version number starts at 1. For each project, I am only concerned with the entry with the highest version number.

For example, if a project with ID 123 has gone through two revisions, there will be three entries with ID 123, each with
incrementing version numbers. I would only be concerned with submission 3 and if it meets my conditions.

ID	Version	Status
123	1	Under Review
123	2	Under Review
123	3	Completed
456	1	Under Review
456	2	Under Review

To give another example based on this table, if I wanted to get check if the latest version of any projects are still under review, I would expect to only get back the row for project 456, version 2.

I've been researching on how to do comparisons between multiple rows but I only feel more confused. Any help would be greatly appreciated.

Re: Compare multiple rows to get the most recent version

amitchandak — Sat, 09 Jul 2022 10:28:10 GMT

Anonymous , Please find the attached solution

Refer to the table with measure Last Status Complete

topic Re: Compare multiple rows to get the most recent version in DAX Commands and Tips

Compare multiple rows to get the most recent version

Re: Compare multiple rows to get the most recent version