If Statement Returns Incorrect Result in New Column

[Anonymous]

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

Specification	Rating
53/80 Name *	*
46/90 Name **	**
46/90 Name ***	***
53/90 Name
53/80 Name ***	***

Current Outcome

Specification	Rating
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]),"***", "")))

[lbendlin · Accepted solution]

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

[Anonymous]

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?

[lbendlin]

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/