Forum Discussion

subguts's avatar
subguts
Regular Visitor
3 years ago

Need help with a measure

Hi, I have a table as shown below.   The StatusEnd column equals different statuses, In Progress, Submitted, etc. The RequestID is the unique ID for a request and therefore can appear multiple tim...
Need Help
Bhu1singh's avatar
Bhu1singh
Frequent Visitor
3 years ago

Hi,

If I understood issue correctly, you need the StatusEnd of each request where modified date is maximum.

This can be achived in Power Query and Dax both.

Power Query follow following steps:

1. Right click the query and create a reference/duplicate of the query

2. In the new Query (which is a duplicate of old one) , in home, go to Group by option 

3. Group the query on Request id and create new column , choose operation "Max" , choose column "modified", click OK.

4. Now go old query and used "Merge Queries" transformation and innerjoin these to table based on request ID and Modified(Date) , you should get the desired output.

(note: joining on datetime column is inefficient better replace it with a key)

let me know if you want dax option as well.

 

Thanks,

Bhuvan

  • subguts's avatar
    subguts
    Regular Visitor
    3 years ago

    Thanks Bhuvan for the quick response,  Yes I would very much like the DAX as it would be prefrerable not to create the duplicate table.  So if there is a way to do this in DAX, I'd like to try that first.

    Thank you so muchBhu1singh !