Forum Discussion

zibster's avatar
zibster
Helper III
8 years ago
Solved

Filter in specific order

Hi, I need to filter out sub_account just to 100 and than find MAX in YEAR_PD and return value from Custom column, I know how to find MAX value but dont know how to apply firs filter to the YEAR_PD t...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi zibster,

     

    Try this formula please.

    Calculations =
VAR maxYearPD =
    CALCULATE ( MAX ( Inv2018T[YEAR_PD] ), Inv2018T[SUB_ACCOUNT] = 100 )
RETURN
    CALCULATE (
        SUM ( Inv2018T[Custom] ),
        FILTER ( ALL ( Inv2018T[YEAR_PD] ), Inv2018T[YEAR_PD] = maxYearPD ),
        Inv2018T[SUB_ACCOUNT] = 100
    )

    Best Regards,

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi zibster,

 

So glad it helps. 

Please refer to the comments in the formula.

 

Calculations =
// We need the max [YEAR_PD] of [SUB_ACCOUNT] 100; CALCULATE changes the contexts of MAX, which
// means we changes the calculation scope of MAX.
VAR maxYearPD =
    CALCULATE ( MAX ( Inv2018T[YEAR_PD] ), Inv2018T[SUB_ACCOUNT] = 100 )
RETURN
// We still use CALCULATE to change the context of SUM. We can set Inv2018T[SUB_ACCOUNT] = 100 in 
// case that other SUB_ACCOUNT has the maxYearPD. 
    CALCULATE (
        SUM ( Inv2018T[Custom] ),
        FILTER ( ALL ( Inv2018T[YEAR_PD] ), Inv2018T[YEAR_PD] = maxYearPD ),
        Inv2018T[SUB_ACCOUNT] = 100
    )
// In short, we reset the calculation scope.

 

Best Regards,

Dale

 

zibster's avatar
zibster
Helper III
8 years ago

Thank you very much.