-4

DAX question

hello. this measure will not work. can you please explain why not?

Lya
  • 33
  • 8
  • Please use text instead of screenshots when sharing code. – Alexis Olson Nov 25 '20 at 18:01
  • 1
    Related: https://stackoverflow.com/questions/64577044 – Alexis Olson Nov 25 '20 at 18:02
  • @AlexisOlson webpage does not allow to copy text from it, so I've used a screenshot instead – Lya Nov 25 '20 at 18:05
  • Is this from some online test or something? It's short enough to quickly be transcribed. – Alexis Olson Nov 25 '20 at 18:07
  • you need to provide more info. there could be any number of reason why... what is your data structure? tables, relationships, datatypes. what output do you get? what do you expect to get? – Nikolaj Klitlund Børty Nov 26 '20 at 10:41
  • It's from online test indeed, so there is no more context – Lya Nov 27 '20 at 05:39
  • 1
    Please **re-read** [How to ask](https://stackoverflow.com/help/how-to-ask), as it would seem that you missed some crucial points the first time you read it, namely "***DO NOT post images of code, data, error messages, etc.** - copy or type the text into the question*" (emphasis in the original). See why [an image of your code is not helpful](http://idownvotedbecau.se/imageofcode). – desertnaut Nov 27 '20 at 09:25

1 Answers1

1

Your measure does not work because you are using a variable as the expression parameter of CALCULATE: variables are immutable; that means that once defined the behave like a constant, that means that their value cannot be changed.

Variables are evaluated where they are defined and not where they are referenced; therefore their value is not affected by modified filter context in CALCULATE.

To check your formula use directly the measure instead of the variable

Sales Last Year =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR( CalendarTable[Date] )
)

This way the [Total Sales] is evaluated in the filter context altered by SAMEPERIODLASTYEAR( CalendarTable[Date] )

using a variable instead, like in

Sales Last Year (wrong) =
VAR Sales = [Total Sales]
RETURN
    CALCULATE(
        Sales,
        SAMEPERIODLASTYEAR( CalendarTable[Date] )
    )

makes Sales to be evaluated outside CALCULATE, using the filter context existing where the measure is evaluated. Assuming that this value is 1000 the following CALCULATE expression is equivalent to

    CALCULATE(
        1000,
        SAMEPERIODLASTYEAR( CalendarTable[Date] )
    )

that will return 1000 whatever the SAMEPERIODLASTYEAR is

sergiom
  • 4,791
  • 3
  • 24
  • 32
  • could you please explain that again, but in simple terms and with examples? This is a kind of question that I always get wrong from an exam, so my understanding of the material is really poor. or perhaps share some resources where this is explained in a way that even I can understand? – Lya Nov 27 '20 at 05:38
  • 1
    I expanded the explanation. A good resource for DAX articles is the site sqlbi.com, where there also is this article about variables in DAX https://www.sqlbi.com/articles/variables-in-dax/ – sergiom Nov 27 '20 at 09:22