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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors