Returning MAX of a column based on value of another column

[KTdataviz]

Good afternoon! I am having a bit of trouble with my DAX syntax for what I'm trying to do. I would like to produce a calculated column with the MAX Tier Number for each Section, based on the Response column. In other words, in the table below, for Section C1, Tiers 1, 2, 3 have a positive response (1), but Tier 4 has a negative response (0), so the MAX Tier Number should be Tier 3. For Section C2, Tiers 1, 2, 3, 4 have a positive response, so the MAX Tier Number should be Tier 4. 

Section Num.	Response	Tier Number	Max Tier
C1	1	1	3
C1	1	2	3
C1	1	3	3
C1	0	4	3
C2	1	1	4
C2	1	2	4
C2	1	3	4
C2	1	4	4

Max Tier = CALCULATE (
    MAX ( 'Survey'[Tier Num.] ),
    ALLEXCEPT ( 'Survey', 'Survey'[Section Num.] ), 'Survey'[Response] = 1
)

 Could someone please help me edit my formula to reflect the desired table above?

Thank you!

[Luca_Yu]

Hi @KTdataviz ,

Please try below DAX to create a new column:

Max Tier =
VAR cur_section = 'Table'[Section Num]
VAR tmp =
    FILTER ( 'Table', 'Table'[Section Num] = cur_section )
RETURN
    CALCULATE ( SUM ( 'Table'[Response] ), tmp )

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

[daXtreme · Accepted solution]

// Let the table be T.
// A good piece of good advice: 
//      Avoid the use of CALCULATE in calculated columns.
// The reason being speed and context transition control.

[Max Tier] = // calc column, not a measure
var CurrentSection = T[Section Num.]
var MaxTierWhereResponseIsOne =
    MAXX(
        FILTER(
            T,
            T[Section Num.] = CurrentSection
            && T[Response] = 1
        ),
        T[Tier Number]
    )
return
    MaxTierWhereResponseIsOne