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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX SUMX and FILTER using the ALL

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

1 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]
)

DAX_SUMX_and_FILTER_using_the_ALL

 

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

5 REPLIES 5
okusai3000
Helper IV
Helper IV

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

 

thanks!

v-jiascu-msft
Employee
Employee

Hi @Anonymous,

 

Could you please mark the proper answers as solutions?

 

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.
v-jiascu-msft
Employee
Employee

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

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.
Anonymous
Not applicable

@v-jiascu-msft

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

 

Regards,

Phil

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]
)

DAX_SUMX_and_FILTER_using_the_ALL

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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