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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.