VALUES function in an example of Introducing Microsoft Power BI Chapter 5

[Anonymous]

Hi everyone,

I'm currently learning how to use Power BI with the book Introducing Microsoft Power BI and I am stuck in Chapter 5.

The example requires a calculation of year-over-year growth.

And the formula in the example is:

New Users Growth =
    IF (
     HASONEVALUE ( Website[Year] ),
     DIVIDE (
     SUM ( Website[New Users] ),
     CALCULATE (
     SUM ( Website[New Users] ),
     Website[Year] = VALUES ( Website[Year] ) - 1
     )
     )
    )

As far as I understand, the HASONEVALUE will return TRUE when the context for columnName has been filtered down to one distinct value only. Then, we carry out the division formula. However, the part I didn't understand is the final line containing the VALUES function. The VALUES function is supposed to return a one-column table. But then, it is followed by "- 1". I don't understand how a table can "minus 1", though I think it is to calculate a growth compared to the previous year.

I have tried to look up the use of VALUES on Microsoft, but the answer seems vague to me. Is there anyone who can help me explain the use of VALUES in this example?

Thanks in advance for your help.

[Anonymous]

@Greg_Deckler Thanks a lot for your help. The new code is clearer for me to understand now with the help of the __PreviousYear variable.

[Greg_Deckler · Accepted solution]

@Anonymous All I can say is that it doesn't seem right but what I suspect is going on is that DAX is doing an internal type conversion or something. DAX does have the ability to convert data types on the fly essentially when necessary. Seems like since it is only returning a single row table and there is a subtraction that DAX is converting it to the value in the row and going on with it's business. If you remove the HASONEVALUE IF statment you can see that this doesn't work when multiple row tables is returned. You get a DAX error that a table of multiple values is being supplied when a single value is expected.

Honestly, I'm a bit surprised in seeing that formula in that book. Relying on internal DAX magic to convert data types on the fly is generally not a good idea. Also, I'm not a fan of using VALUES as it can return a blank row and I would personally rather use DISTINCT. But, I wouldn't use either in that formula, I would use MAX or MIN instead. But, MAX and MIN can't be used in your filter clause of CALCULATE. But, then again, I wouldn't use CALCULATE either if I could avoid it. 

Overall, that's not a great formula in my book but that's one person's opinion. Now, not to simply criticize someone else's code but provide an alternative you could do this instead and in my opinion it would be cleaner:

New User Growth =
  IF(HASONEVALUE('Website'[Year]),
      VAR __PreviousYear = MAX('Website'[Year]) - 1
    RETURN
      DIVIDE(
        SUM('Website'[New Users]),
        CALCULATE(
          SUM('Website'[New Users]),
          'Website'[Year] = __PreviousYear
        )
      )
  )

Now, in the authors' defense, the book may have pre-dated VAR and RETURN statements. In my defense, I still wouldn't have written it as above but that's in keeping with the spirit of the original author's code.