Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

If Statement Returns Incorrect Result in New Column

I am attempting to add a new column which looks through the specification column and returns the details in the rating column.  I have tried this two different ways but neither seems to work as all that is returned is *.

 

Desired Outcome

SpecificationRating
53/80 Name * *
46/90 Name ****
46/90 Name ******
53/90 Name  
53/80 Name ******

 

Current Outcome

SpecificationRating
53/80 Name * *
46/90 Name ***
46/90 Name ****
53/90 Name  
53/80 Name ****

 

Method 1 Attempted in Query Editor:

 

if Text.Contains([Specification],"*") then "*" else if Text.Contains([Specification],"**") then "**"
else if Text.Contains([Specification],"***") then "***" else ""

 

Method 2 Attempted by using Add Custom Column to the table:

 

Rating= if(SEARCH("*",Table1[Specification]),"*", if(SEARCH("**",Table1[Specification]),"**",if(SEARCH("***",Table1[Specification]),"***", "")))
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

You will want to reverse your checks. Check for *** first, then for ** , then for * .

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

You will want to reverse your checks. Check for *** first, then for ** , then for * .

Anonymous
Not applicable

Thank you very much for that. Can you please explain why this worked? 

 

Is it because in the way I had it set up originally the function would find the first * in the string return the defined result for that and then stop iterating through the rest of the if statements?

yep, you got it.  The SWITCH() statement in DAX does the same. As soon as it has a match it exits.  

 

Actually there is a bit more to your question than you might think.  Here's a very interesting link

https://www.sqlbi.com/articles/understanding-eager-vs-strict-evaluation-in-dax/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors