Combine IF, HASONEVALUE and Value functions query page. 107

This is a dedicated forum for people that have questions about the book Supercharge Power BI. In the event of errors in the book, the error information will be in this forum.
cindychan01
Posts: 7
Joined: Tue Jan 26, 2021 2:02 pm

Combine IF, HASONEVALUE and Value functions query page. 107

Postby cindychan01 » Tue Apr 06, 2021 9:37 pm

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
Posts: 70
Joined: Thu Oct 19, 2017 12:57 pm

Re: Combine IF, HASONEVALUE and Value functions query page. 107

Postby RamanaV » Wed Apr 07, 2021 10:12 am

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])
Ramana Varanasi
Training and Office Manager
Excelerator BI Pty. Ltd.

cindychan01
Posts: 7
Joined: Tue Jan 26, 2021 2:02 pm

Re: Combine IF, HASONEVALUE and Value functions query page. 107

Postby cindychan01 » Wed Apr 07, 2021 10:44 am

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
Posts: 70
Joined: Thu Oct 19, 2017 12:57 pm

Re: Combine IF, HASONEVALUE and Value functions query page. 107

Postby RamanaV » Wed Apr 07, 2021 10:58 am

Can you please take a snapshot of the formula in formula bar along with the error?
Ramana Varanasi
Training and Office Manager
Excelerator BI Pty. Ltd.

cindychan01
Posts: 7
Joined: Tue Jan 26, 2021 2:02 pm

Re: Combine IF, HASONEVALUE and Value functions query page. 107

Postby cindychan01 » Wed Apr 07, 2021 11:09 am

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
Posts: 70
Joined: Thu Oct 19, 2017 12:57 pm

Re: Combine IF, HASONEVALUE and Value functions query page. 107

Postby RamanaV » Wed Apr 07, 2021 11:54 am

Please put it in the website enquiry form. We will address it
Ramana Varanasi
Training and Office Manager
Excelerator BI Pty. Ltd.

MattAllington
Posts: 1158
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Combine IF, HASONEVALUE and Value functions query page. 107

Postby MattAllington » Wed Apr 07, 2021 8:00 pm

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.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training


Return to “Help with the Book "Supercharge Power BI"”

Who is online

Users browsing this forum: No registered users and 1 guest