cancel
Showing results for
Did you mean:
Frequent Visitor

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

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?

1 ACCEPTED SOLUTION
Super User

@dididing2001 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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition
2 REPLIES 2
Frequent Visitor

@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.

Super User

@dididing2001 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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition

Announcements

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

#### Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!