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
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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors