Create a column that shows the max of another column

[SimonDoras]

Hello everyone,

I'm new to Power Bi, and usually I quickly find an answer to my beginner's questions. But here whether it's with the forums, the official doc or even chatGPT I can't find it.. whereas I think my question is rather simple.

I have several tables that are all in the same format.

In column: Title | Column A | Column B | ID

-> The ID is necessarily different (increment automatically in my initial database). The two columns that I would like to add (I think it's easier by breaking it down):

- A "MaxID" column that indicates the highest ID for all rows with the same title.

- An "Active" column with "yes" or "no" if the maxID is equal to the row ID in question.

An example of a table before transformation:

Title	Column A	Column B	Id
AA	abcd	abcd	1
AA	abcd	abcd	2
BB	abcd	abcd	3
CC	abcd	abcd	4
CC	abcd	abcd	5
AA	abcd	abcd	6

The result I expect:

Title	Column A	Column B	Id	MaxID	Active
AA	abcd	abcd	1	6	no
AA	abcd	abcd	2	6	no
BB	abcd	abcd	3	3	yes
CC	abcd	abcd	4	5	no
CC	abcd	abcd	5	5	yes
AA	abcd	abcd	6	6	yes

Hoping to have been clear, thank you for your help!

Simon

[Ahmedx · Accepted solution]

pls try this

[SimonDoras]

It's perfect ! 

A question, what is the advantage or the disadvantage of doing it in the table directly and not in the "transform the data" part?

[Ahmedx]

if you want to know how to implement this in POWER QUERY then watch my video

https://1drv.ms/v/s!AiUZ0Ws7G26RiSpsfTgnxMVCfJLB?e=S7zoVp

(x)=> Table.AddColumn(x,"MAXID", each List.Max(x[Id]))

[danextian]

Hi @SimonDoras 

The DAX solution proposed by @Ahmedx is faster than doing it in PQ as DAX is more optimized at scanning a table than M