cancel
Showing results for
Did you mean:
Regular Visitor

## Calculated column and measure giving different results

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]
)
)

This is the power bi file

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
2 REPLIES 2
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Regular Visitor

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.

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors