Return a single value from a column based on a measure DAX

[Anonymous]

Hello ,
I am trying to replace the unassigned value in type column with the value of type of max program. This need to be done on calculated column As showing in New Type Colum required.

ID, Program,Type, New Type

117	32	#	MST
117	679	MST	MST

Here is the DAX code:

New Type =

//Get Max Program

var MaxProgram = MAX('Table22'[Program])

//Get Max Program by ID

var MaxProgramByID = CALCULATE(MaxProgram,ALLEXCEPT('Table22','Table22'[ID]))

//Get The type of Max Program by ID

var NewType=SUMMARIZE (FILTER('Table22',('Table22'[Program]=MaxProgramByID)),'Table22'[Program],'Table22'[ID])

Return

if('Table22'[Type]="#",NewType,'Table22'[Type])

Anyhow, it gives this error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

[amitchandak]

@Anonymous , try like

//Get The type of Max Program by ID

var NewType=SUMMARIZE (FILTER('Table22',('Table22'[Program]=MaxProgramByID)),'Table22'[Program])

Return
if('Table22'[Type]="#",maxx(NewType,[Program]),max('Table22'[Type]))

[Anonymous]

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

Please try code as below to create a calculated column.

New Type = 
//Get Max Program by ID
VAR MaxProgramByID =
    CALCULATE ( MAX ( 'Table22'[Program] ), ALLEXCEPT ( 'Table22', 'Table22'[ID] ) ) //Get The type of Max Program by ID
VAR NewType =
    MAXX (
        FILTER ( ALLEXCEPT ( Table22, Table22[ID] ), Table22[Program] = MaxProgramByID ),
        [Type]
    )
RETURN
    IF ( 'Table22'[Type] = "#", NewType, 'Table22'[Type] )

Result is as below.

Best Regards,
Rico Zhou

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