cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gsraje
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]
)
)
 

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

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
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.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
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.

View solution in original post

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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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