calculated column multiple if statements to find the maximum value

[vishnuprashanth]

My dataset has different categories for the attribute column. I need to find the maximum value for each category for each person for each week. 

I am trying to create a calculated column to get the maximum values for each category in Attribute for each person and each week. I tried and wrote the below query, which I know is not an efficient way of code & also it doesn't work...

Maximum values =

SWITCH (
TRUE (),
   
    ( 'TABLE'[Attribute] == 'total energy(J/Kg)'
     MAXX (
     FILTER (
        TABLE,
        TABLE[Athlete] = EARLIER ( TABLE[Athlete] )
            && TABLE[WeekNumber] <= EARLIER (TABLE[WeekNumber] ) -1 ),
    TABLE[value)],


     'TABLE'[Attribute] == 'total distance(m)'
     MAXX (
     FILTER (
        TABLE,
        TABLE[Athlete] = EARLIER ( TABLE[Athlete] )
            && TABLE[WeekNumber] <= EARLIER (TABLE[WeekNumber] ) -1),
    TABLE[value)]

       ...........("""like this for each category in the Attribute column""")
))

Can someone help me write the correct formula for having this calculated column? or creating a measure is the correct method to solve this problem? 

TIA. 

[Zubair_Muhammad · Accepted solution]

HI @vishnuprashanth

You can attribute condition to your formula

Max Energy Target =
MAXX (
    FILTER (
        TABLE,
        TABLE[attribute] = EARLIER ( TABLE[attribute] )
            && TABLE[athlete] = EARLIER ( TABLE[athlete] )
            && TABLE[WeekNumber]
                <= EARLIER ( TABLE[WeekNumber] ) - 1
    ),
    TABLE[total energy (J/kg)]
)

[vishnuprashanth · Accepted solution]

Max Value = 


CALCULATE (
MAX ( PT2[Value] ),
FILTER ( ALLEXCEPT ( PT2,PT2[Attribute], PT2[athlete] ), PT2[WeekSequence] = EARLIER ( PT2[WeekSequence] ) -1 )
)

I tried this code and it works correctly as I wanted I think. I will just and verify again. Thanks @Zubair_Muhammad 

[Zubair_Muhammad]

HI @vishnuprashanth

Try this one both as a MEASURE and as a calculated column

Maximum Value =
CALCULATE (
    MAX ( TableName[Value] ),
    ALLEXCEPT (
        TableName,
        TableName[Attribute],
        TableName[Athlete],
        TableName[WeekNumber]
    )
)

[vishnuprashanth · Accepted solution]

Max Value = 


CALCULATE (
MAX ( PT2[Value] ),
FILTER ( ALLEXCEPT ( PT2,PT2[Attribute], PT2[athlete] ), PT2[WeekSequence] = EARLIER ( PT2[WeekSequence] ) -1 )
)

I tried this code and it works correctly as I wanted I think. I will just and verify again. Thanks @Zubair_Muhammad 

[vishnuprashanth]

@Zubair_Muhammad Thanks, Zubair.

Both are working on calculating the max values. But, It is updating & taking into account of the max values for the present week only. 

But, I want to take the best(max values) of all the weeks, and compare that value with the present week's values. 

Say, for calculating the max energy alone, when my table was not unpivoted, I used the code below 

Max Energy Target = 
MAXX (
    FILTER (
        TABLE,
        TABLE[athlete] = EARLIER ( TABLE[athlete] )
            && TABLE[WeekNumber] <= EARLIER (TABLE[WeekNumber] ) -1
            
    ),
    TABLE[total energy (J/kg)]
)

Now, I want this same function to be applied to each category in the Attribute column.

[Zubair_Muhammad · Accepted solution]

HI @vishnuprashanth

You can attribute condition to your formula

Max Energy Target =
MAXX (
    FILTER (
        TABLE,
        TABLE[attribute] = EARLIER ( TABLE[attribute] )
            && TABLE[athlete] = EARLIER ( TABLE[athlete] )
            && TABLE[WeekNumber]
                <= EARLIER ( TABLE[WeekNumber] ) - 1
    ),
    TABLE[total energy (J/kg)]
)

[Zubair_Muhammad · Accepted solution]

@vishnuprashanth

With the MEASURE i wrote you can  remove WeekNumber from ALLEXCEPT to get max value for all weeks

Maximum Value for All Weeks =
CALCULATE (
    MAX ( TableName[Value] ),
    ALLEXCEPT ( TableName, TableName[Attribute], TableName[Athlete] )
)

[vishnuprashanth]

Both the calculated column and measure works perfectly!! Thanks a lot, @Zubair_Muhammad. 

Just one small suggestion I require. I would be refreshing my data every day. So, Which one(column or measure) will be the best option for calculating these values? or both are gonna have the same effect? 

Thanks and Regards,
Vishnu