Max Value in column for each group

[hwoehler]

Hello everyone,

I am looking for a solution for the following situation:
I have the following table (Date and calculated column Seq) with a linked DateTable. I would like to have the highest value displayed for each group. The empty fields between the number sequences separate the individual groups. To illustrate this, there is the column "result".
Maybe you could create another column with the groups A,B,C (column „Groups“)etc. and filter them?

Date	Seq	result	Groups
02/05/2019
02/06/2019	1		A
02/09/2019	2	2	A
02/10/2019
02/11/2019	1		B
02/12/2019	2		B
02/13/2019	3		B
02/16/2019	4	4	B
02/17/2019
02/18/2019
02/19/2019	1	1	C
02/20/2019
02/23/2019	1		D
02/24/2019	2		D
02/25/2019	3	3	D

I would appreciate any help!
Regards,
hwoehler

[hwoehler · Accepted solution]

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

[v-yuta-msft · Accepted solution]

@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.

[Gordonlilj]

Hi,

If i understood you correctly then creating a calculated column using somthing like the code below should give the result you illustrated

MaxValue =

IF( 'Table'[Seq] = CALCULATE( MAX( 'Table'[Seq] ), 

                              ALLEXCEPT( 'Table', 'Table'[Groups] ) ), 'Table'[Seq]

)

[hwoehler · Accepted solution]

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