Max Value in column for each group

Hi Gordonlilj,

thank you very much for your quick reply.

Sorry, I forgot to make it clear that the "Groups" column does not exist yet. This would have to be created or it must be recognized how the groups are composed. That's my problem right now. Regards, hwoehler

hwoehler ,

It's not easy to generate the Group column using power query or dax, so I would suggest you use python code to get the Group column, you may refer to the code below:

# 'dataset' holds the input data for this script

import pandas as pd

Seq= dataset['Seq'].values.tolist()
Group = []
start = 0

for ele in Seq:
    if ele == 1:
        start = start + 1
        Group.append(start)
    else:
        Group.append(start)

dataset['Group'] = Group

Then create a custom column using M code below to beautify the Value.Group column:

if [Value.Seq] <> "" and [Value.Group] = "1" then "A" else if [Value.Seq] <> "" and [Value.Group] = "2" then "B" else if [Value.Seq] <> "" and [Value.Group] = "3" then "C" else if [Value.Seq] <> "" and [Value.Group] = "4" then "D" else null

After that, remove the useless columns and create a calculate column using dax below:

Result = CALCULATE(MAX('Table'[Seq]), ALLEXCEPT('Table', 'Table'[Group]))

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.