Forum Discussion

rido's avatar
rido
Regular Visitor
2 years ago
Solved

DAX query behavior explanation

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's avatar
    tamerj1
    2 years ago

    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. 

4 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    2 years ago

    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's avatar
      rido
      Regular Visitor
      2 years ago

      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's avatar
        tamerj1
        Community Champion
        2 years ago

        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.