DAX query behavior explanation

[rido]

Hi,
I have a table of dates and respective values associated with the dates:

• March 12, 2021: 22,393 unit sales
• March 13, 2021: 21,320 unit sales
• March 14, 2021: 21,927 unit sales
• March 15, 2021: 21,690 unit sales

I have created a DAX formula that returns the max values among dates 12th and 14th: 

highlight2 =

    MAX(SUMX(FILTER(sales,sales[date]= DATE(2021,03,12)), sales[unit_sales]),SUMX(FILTER(sales,sales[date]= DATE(2021,03,14)), sales[unit_sales]))

Now this formula works fine and returns the highest value 22393 in a card visualization when no date field is added-

But when I add the date field it returns the values for those 2 dates i.e. 12th and 14th

Can someone please explain why these might be happening?

Thanks!

[tamerj1]

Hi @rido 

please try

highlight2 =
MAX (
SUMX (
FILTER ( ALL ( sales ), sales[date] = DATE ( 2021, 03, 12 ) ),
sales[unit_sales]
),
SUMX (
FILTER ( ALL ( sales ), sales[date] = DATE ( 2021, 03, 14 ) ),
sales[unit_sales]
)
)

[rido]

Hello,
Thank you for the response 🙂

I can see that the updated formula returns the max value among all the dates for each date but I just wanted to understand why the original formula returned the values associated with the 2 dates only when the date field had been introduced.

[tamerj1 · Accepted solution]

@rido 

When you place a column in a visual it acts as a filter. The subset of sales table that is visible in the first row is slready filtered to the date in the same row and so on. ALL removes the filter snd returns the complete table. 

[rido]

Yes, that seems like it. 
Thanks a lot! 🙂