dax creating new column using max of group

[Anonymous]

I have data as below. I want to create the last 2 columns using dax

Calculation as follows -
 
for the c2-max column, for each distinct value in the first column, keep the max value in column c1
for the boolean column, if c2-max column is greater than 0 then 1 else nothing

PK	batch	batchstatus	c1	c2-max	boolean
1	1	progress	1
1	2	comp
1	3	comp
1	4	comp
1	5	progress	5	5	1
2	1	progress	1
2	2	progress	2		1
2	3	null	3	3
3	1	progress	1
3	2	progress	2
3	3	progress	3	3	1
4	1	progress	1
4	2	progress	2
4	3	progress	3
4	4	progress	4
4	5	progress	5	5	1
4	6	progress

[MFelix · Accepted solution]

Hi @Anonymous ,

Try the following code:

c2-max =
IF (
    'Table'[c1       ]
        = CALCULATE (
            MAXX ( 'Table', 'Table'[c1       ] ),
            ALLEXCEPT ( 'Table', 'Table'[PK       ] )
        ),
    'Table'[c1       ]
)

boolean = IF( 'Table'[c2-max] > 0 , 1)

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

You can also try to operate in the query editor, refer to this sample.

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

[MFelix · Accepted solution]

Hi @Anonymous ,

Try the following code:

c2-max =
IF (
    'Table'[c1       ]
        = CALCULATE (
            MAXX ( 'Table', 'Table'[c1       ] ),
            ALLEXCEPT ( 'Table', 'Table'[PK       ] )
        ),
    'Table'[c1       ]
)

boolean = IF( 'Table'[c2-max] > 0 , 1)