Combine IF, HASONEVALUE and Value functions query page. 107

[cindychan01]

Page 107 - Product Name(Values) =
IF(HASONEVALUE(Products[ProductName]),
VALUES(Products[ProductName])
)

When I tried to type VALUE(Text string - Converts a text string that represents a number to a number), I have to manually type the word -(Products[ProductName}). It did not seem to be normal to type this way. Anyway, this ended up to have an errors message.

Page 106 - Month Name (Values) =
IF(HASONEVALUE('Calendar'[MonthName],
VALUES('Calendar'[MonthName])
)
After VALUE(, I manually type ('CALEDAR'[MONTHNAME], the formula works.

As the book says, there is more than one value in the Value (), the formula returns an error. But why P.106 works but not P.107 formula. This is puzzle me about.

[RamanaV]

Please check what you typed - (Products[ProductName})
You typed a curly brace and it did not work I think
It should have been (Products[ProductName])

[cindychan01]

Hi Ramana

The formula bar - Value(Products[ProductName]) has a red line underline of Product Name.

Simply I typed the same as the book state. There is an error message "A single value for column 'ProductName' in table 'Products' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

However, when I typed page 106 for the same formula, I don't have any issue. That is why I don't understand why this happened.

[RamanaV]

Can you please take a snapshot of the formula in formula bar along with the error?

[cindychan01]

Sorry, I am unable to post the attachment for you because I have already reached the broad index limited less you have emailed that I can email it to you directly.

[RamanaV]

Please put it in the website enquiry form. We will address it

[MattAllington]

Hi Cindy

The errors come from Intellisense. Intellisense looks at the function you are typing, it senses the valid syntax and will only allow you to write valid syntax.

There are 2 functions.
VALUE
VALUES

VALUE only accepts a text string, not a column.
VALUES only accepts a column or table, not a text string.

You can’t interchange these things.

There is no VALUE() function covered on page 107, only VALUES(). When you write the function using VALUES, you don’t get an error.