Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear All,
I am trying to perform an if test on a column that contains strings. After searching the web, I decided to use the search function.
I keep running in an error telling me that a single value cannot be determined (please see snap shot below) I know for a fact that what I am looking for does exist and only appears once in the entire database! the function is here :
test = if(SEARCH("Transient Retail",'home accounts'[acc_name],,0)<>0,10,0)
Many thanks,
Solved! Go to Solution.
Is home accounts{acc_name] already a calculated column?
Try this one as a column
test = IF(IFERROR(SEARCH("Transient Retail",'home accounts'[acc_name]),-1)=1,10,0)
Hi @YGoe
Try this instead
test = IF(IFERROR(SEARCH("Transient Retail",'home accounts'[acc_name],-1)=1,10,0)
Note this: The search function is case insensitive
Hi @sokg,
I tried what you proposed and the error remains.
there is an error that is linked to the column 'home accounts'[acc_name] as it has a red line underneath it.
I honestly do not know why. You may find below a snapshot of the table accounts
Could you give a print screen from the error you get with my formula
At the end you have 2 parenthesis. You need only one.
I already tried removing brackets and moving the ,0 outside the first bracket. but it is still not working.
I dont know why the selected column is not being recognised properly.
is there another way to perform an if test on varchar datatype instead of numeric?
thanks,
Not brackets[ ] , i said parenthesis ( ) . You have two at the end.
Sorry my mistake, it was missing one parethesis after your column name
try this
test = IF(IFERROR(SEARCH("Transient Retail",'home accounts'[acc_name]),-1)=1,10,0)
no problem 🙂
it is a tough one. I tried a lot of things before asking for help.
Your new solution return the following error:
There must be an issue with the column in the table but it does not make any sense to me. the snapshot of the account table shows that the column Acc_name does exist and the text we are searching for is in that column!!
I dont really know what to do.
Are you creating a column or a measure ?
it is a measure. I tried creating a calculated column but the operation that follows the if statement creates a circular reference.
Is home accounts{acc_name] already a calculated column?
Try this one as a column
test = IF(IFERROR(SEARCH("Transient Retail",'home accounts'[acc_name]),-1)=1,10,0)
it works when creating a column 🙂
why does it fail when creating a new measure then?
Because needs a row contex to work. When you try as a measure tries to read the whole column .
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |