cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
analyst85
Regular Visitor

Custom column using IF/AND

Hello

 

I'm attempting to create a custom column based on the values of two existing columns (Score, Name) however I only get zeroes when using the below query and can't understand why?

 

if [Score = "3" and [Name] = "RT01" then "This service has alot of radios" else if [Score] = "3" and [Name] = "RT02" then "This service has less radios" else if [Score] = "3" and [Name] = "J01" then "This service has alot of jobs" else if [Score] = "3" and [Name] = "J02" then "This service has less jobs" else 0

1 ACCEPTED SOLUTION
mahoneypat
Super User IV
Super User IV

The first [Score  is missing a ] but i assume that is a type.  If the Score column is numeric you need just put the number without quotes  - [Score] = 3

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
HotChilli
Super User II
Super User II

So we can see that all rows are falling through to the 'else' clause and returning 0.

If you want to debug this write a new, simple custom column with a single test - if, then, else

and try to make that work.

for example: if [Score] = "3" then "success" else "fail"

----

Looking at your code, I can guess that it may return all "fail" - see if you can make it work

mahoneypat
Super User IV
Super User IV

The first [Score  is missing a ] but i assume that is a type.  If the Score column is numeric you need just put the number without quotes  - [Score] = 3

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Thanks, the removal of quotes helped but I only get a succesful outcome if the first IF statement is met. However if the second statement is met it doesn't work and gives me zeroes still. Is there something I need to do to ensure each statement is checked?

Nevermind it has actually worked, thanks so much!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors