creating new columns conditionally

[viitama]

Hi, I'm struggling with references and conditions. Here is my table and I wan't te recreate Res column.

ID	subID	Vol	Res
A	aa	1000	ac
A	ab	250	ac
A	ac	5000	ac
B	aa	50	ab
B	ab	6000	ab
B	ac	300	ab

Res column is created by grouping by ID and selecting subID that has maximum Vol. Moving to Power BI from R where these reference seems logical, but here I can't get them to work at all.

Here is my try, but giving just blanks.

Res = 
    CALCULATE (
        SELECTEDVALUE ( subID ),
        FILTER ( ALLEXCEPT ( 'Table', ID ), Vol = MAX(Vol) )
    )

[Zubair_Muhammad · Accepted solution]

hi @viitama

Try the same pattern I gave you last time

Res =
VAR MaxFig =
    CALCULATE ( MAX ( 'Table'[Vol] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
RETURN
    CALCULATE (
        VALUES ( 'Table'[subID] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), 'Table'[Vol] = MaxFig )
    )

[viitama]

I had something like that, but it is giving me error "A table of multiple values was supplied where a single value was expected". Not sure why that fails. Actually change VALUES to SELECTEDVALUE and it works. Thanks.

[Zubair_Muhammad]

@viitama

VALUES would throw an error if there are same sub IDs with Max Vol for a particular ID.