Calculated column and measure giving different results

[gsraje]

Hi, I am trying to create a calculated column which will populate measure values only for the maximum index per ID. I'm able to achieve the same functionality using a measure. While creating a calculated column with similar logic, the value gets populated for few more rows than expected.

The measure I have used in calculated column and measure

Max Value =

CALCULATE(

MAX(

Sheet1[Value]

),

ALLEXCEPT(

Sheet1,

Sheet1[ID]

),

FILTERS(Sheet1[Type1]),

FILTERS(Sheet1[Type2]),

FILTERS(Sheet1[Type3]),

FILTERS(Sheet1[Type4])

)

The intermediate measure to calculate max index

Max Index =

CALCULATE(

MAX(

Sheet1[Index]

),

ALLEXCEPT(

Sheet1,

Sheet1[ID]

),

FILTERS(Sheet1[Type1]),

FILTERS(Sheet1[Type2]),

FILTERS(Sheet1[Type3]),

FILTERS(Sheet1[Type4])

)

The measure which gives expected output

Max Value Single Measure =

CALCULATE(

IF(

MAX(Sheet1[Index]) = [Max Index],

[Max Value]

)

)

The calculated column dax

Max Value Single col =

CALCULATE(

IF(

MAX(Sheet1[Index]) = [Max Index],

[Max Value]

)

)

Please help me understand why it is giving additional values and how can I fix it.

This is the power bi file

https://drive.google.com/file/d/1pwRRbnEdAHq_8_rAxqlJnCaPhSNjKkfU/view?usp=sharing

[v-shex-msft · Accepted solution]

HI @gsraje,

You can use following DAX formula to achieve your requirement:

Max Value Single col 2 =
IF (
    Sheet1[Index]
        = MAXX ( FILTER ( Sheet1, [ID] = EARLIER ( Sheet1[ID] ) ), [Index] ),
    MAXX ( FILTER ( Sheet1, [ID] = EARLIER ( Sheet1[ID] ) ), [Value] )
)

Regards,
Xiaoxin Sheng

[gsraje]

Hi @v-shex-msft ,

This formula works fine if there are no filters applied. But if I apply filters, it gives blank values in certain cases. Still, thank you so much for the help.