Apply Max from one column to all the matching rows of same ID in another column

[jean004]

ID	Term	Is Current Term	Current Term	Max Active Term of ID
1	1			2
1	2	Yes	2	2
1	3			2
2	1			3
2	2			3
2	3	Yes	3	3
2	4			3
2	5			3

I have a table something to the above, where I have IDs of contracts.  There is a contract term field term (with additional start/end dates in other columns) and I have a field that tells me what is the active term of the contract.    I created a column to return to me the Current Active term.  However, I do not understand/know how to get the last column which would apply the max term to all of the matching ID rows.  Sort of like a MaxIF statement.

[jean004]

Thank you.  appreciate this.   Hopefully this isn't a bad question.  I know how to run Measure using PowerPivot, however I can't remember at this point how to accomplish this in PowerQuery.

In the meantime, I just completed trying a different way, and this was creating a function of the query up to the point of my table, and then creating a grouping with Max for dates and Term to get the results I wanted and merging them back in the previous query.  It works as I was hoping but it does take a bit of time to process and refresh everything.

In your opinion would using the method above work better then what I have done?

[Chew_WenJie]

Hi, you may try this measure 

Measure = CALCULATE(MAX(Table[Current Term]),ALLEXCEPT(Table,Table[ID]))

Hope this can help you.