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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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