DAX SUMX and FILTER using the ALL

[Anonymous]

Hi All,

Have an issue where I have the below measure:

YTD_Store_Open =
CALCULATE(
SUMX(VALUES('Store Mapping'[Store]),
CALCULATE(SUM('ERP Sales'[Sales]),
FILTER(ALL('Date Dimension')
,'Date Dimension'[Date] >= [Store_MinDate])
)
)
)

This works as intended except when totalling, because the date filter for [Store_MinDate] looks over the entire data set rather then on a by store basis. Hence I implemented a SUMX to do a row by row calc to let the totals work correctly. 

YTD_Store_Open_CompOnly =
CALCULATE(
SUMX(VALUES('Store Mapping'[Store]), [YTD_Store_Open]),
FILTER('ERP Sales',[Comp Flag]=1),
FILTER('Store Mapping',[Close_Flag] = 0)
)

Now when I have no Month or date selected, this works as intended, however I cannot fix my measures in that when a user selects a month it filters measure 'YTD_Store_Open_CompOnly ' even though I don't want it to (the measure YTD_Store_Open works as intended excetp for totals)

How can I update my SUMX or use a similar measure so that I can calculate the SUM TOTALS on a row by row basis but ignore a date filter selection.

Note: I have tried using ALL('Date Dimension') in the second measure as well as the same filter from my first expression, i.e.
YTD_Store_Open_CompOnly = 
CALCULATE(
SUMX(VALUES('Store Mapping'[Store]), [YTD_Store_Open]),
FILTER('ERP Sales',[Comp Flag]=1),
FILTER('Store Mapping',[Close_Flag] = 0),
ALL('Date Dimension')

OR

YTD_Store_Open_CompOnly = 
CALCULATE(
SUMX(VALUES('Store Mapping'[Store]), [YTD_Store_Open]),
FILTER('ERP Sales',[Comp Flag]=1),
FILTER('Store Mapping',[Close_Flag] = 0),
FILTER(ALL('Date Dimension')
,'Date Dimension'[Date] >= [Store_MinDate])
)
)

Thanks

[okusai3000]

Hello, i'm strugglig with the same problem, and the proposed solution doesn't work for me. Any idea?

thanks!

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[v-jiascu-msft]

Hi @Anonymous,

Can you share the file? DAX formula responds to the context. So it hard to trouble shoot with a bare formula. Did you try to recognize the Total context with "Hasonevalue"?

Best Regards,

Dale

[Anonymous]

@v-jiascu-msft

I will send in a private message due to data sensitivity.

Regards,

Phil

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Please try the measure below. Please check out your Message for details.

YTD_Summarize =
SUMX (
    SUMMARIZE ( 'Store', [P], [S], "Value", [YTD_Store_Open] ),
    [Value]
)

Best Regards,

Dale