Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
zibster
Helper III
Helper III

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 the calculation should return last row as valid row.

 

 

YEAR_PDSUB_ACCOUNTDESCRIPTIONFACDEPTSECTAMTYearPerStoreSectSpread2018T4.%Custom
2018 04312166INVENTORY8301301 2018 040008301 ($160,886.00)
2016 07100INVENTORY COUNT83013011394412016 07000830165.29%$139,441.00
2016 13100INVENTORY COUNT83013011490262016 13000830164.39%$149,026.00
2017 07100INVENTORY COUNT83013011464702017 07000830165.12%$146,470.00
2017 13100INVENTORY COUNT83013011393692017 13000830165.11%$139,369.00

 

 

 

 

1 ACCEPTED SOLUTION

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

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

View solution in original post

6 REPLIES 6
zibster
Helper III
Helper III

Hi,

 

I am trying to filter first all accounts down to only 100 and then return only the value with the max year_pd? the below returns correct Year_PD  but i don't know how to filter the account please help.

 

Thanks

Z

 

Calculations:=CALCULATE(SUM(Inv2018T[Custom]),
                FILTER(ALL(Inv2018T[YEAR_PD]),Inv2018T[YEAR_PD]=MAX(Inv2018T[YEAR_PD]))
                )

 

 

 

YEAR_PDSUB_ACCOUNTDESCRIPTIONFACDEPTSECTAMTYearPerStoreSectSpread2018T4.%Custom
2018 04312166INVENTORY-MERCHANDISE-CLOSING8301301 2018 040008301 ($160,886.00)
2016 07100INV OVER/(SHORT) - TOTAL INVENTORY COUNT BY DEPT83013011394412016 07000830165.29%$139,441.00
2016 13100INV OVER/(SHORT) - TOTAL INVENTORY COUNT BY DEPT83013011490262016 13000830164.39%$149,026.00
2017 07100INV OVER/(SHORT) - TOTAL INVENTORY COUNT BY DEPT83013011464702017 07000830165.12%$146,470.00
2017 13100INV OVER/(SHORT) - TOTAL INVENTORY COUNT BY DEPT83013011393692017 13000830165.11%$139,369.00
zibster
Helper III
Helper III

Here is my formula but returns (blank)

 

Calculations:=CALCULATE(SUM(Inv2018T[Custom]),FILTER(ALL(Inv2018T[YEAR_PD]),Inv2018T[YEAR_PD]=MAX(Inv2018T[YEAR_PD])),Inv2018T[SUB_ACCOUNT]="100")

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

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

Hi Dale,

Works like a charm, would you have time to let me know how this works?

 

Thanks

Z

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

 

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

Thank you very much.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.