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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
YGoe
New Member

Search text DAX not working

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, 

 

Capture.PNG

1 ACCEPTED SOLUTION
sokg
Solution Supplier
Solution Supplier

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)

 

View solution in original post

15 REPLIES 15
sokg
Solution Supplier
Solution Supplier

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

 

Capture 2.PNG

sokg
Solution Supplier
Solution Supplier

Could you give a print screen from the error you get with my formula

 

@sokg there you go. 

 

Capture 3.PNG

sokg
Solution Supplier
Solution Supplier

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,

sokg
Solution Supplier
Solution Supplier

Not brackets[ ] , i said parenthesis ( ) . You have two at the end. Capture.JPG

@sokg it is not solving the issue

 

Capture 4.PNG

sokg
Solution Supplier
Solution Supplier

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:

 

 

Capture 5.PNG

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.

sokg
Solution Supplier
Solution Supplier

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. 

sokg
Solution Supplier
Solution Supplier

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?

sokg
Solution Supplier
Solution Supplier

Because needs a row contex to work. When you try as a measure tries to read the whole column .

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.