cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dididing2001
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.

Screenshot 2021-09-01 004723.png

 

 

 

 

 

 

 

 

 

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.

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
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.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

View solution in original post

2 REPLIES 2
dididing2001
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.

Greg_Deckler
Super User
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.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

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

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

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

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

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

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors