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
Anonymous
Not applicable

Fetch all the data from a column if particular text appears in the column values

Hi All,

I am trying to make a column based on the values of other column. As you can see from my example below.

Now I have a problem where i have many r_info[text] which are start with keine, for eg. keine this, keine that, etc etc. 
I want to select them all in the IF condition.

How can I achieve this ?

Red text is giving me this error "DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values."

 

balcon = IF
(r_info[info] = "balcon" ||
r_info[text] = "" ||
r_info[text] = "-" ||
r_info[text] = "k.A." ||
r_info[text] = SEARCH("keine*", r_info[text],1,"other") >0,
"No",
"Yes")

1 ACCEPTED SOLUTION

Hi again

 

The error is because of the fourth parameter in the SEARCH function "other"

Its recommended to use 0 for that parameter value.

You can go through the below link to get more insights for the reason of the error.

https://msdn.microsoft.com/en-us/query-bi/dax/search-function-dax

 

Updated formula-

balcon = IF
(r_info[info] = "balcon" ||
r_info[text] = "" ||
r_info[text] = "-" ||
r_info[text] = "k.A." ||
SEARCH("keine", r_info[text],1,0) >0,
"No",
"Yes")

View solution in original post

5 REPLIES 5
danextian
Super User
Super User

 

SEARCH("keine*", r_info[text],1,"other") >0

 

The problem with this formula is that if you use if the workd keine cannot be found in the text column, it will return the word other.  While your SEARCH parameter is syntactically correct, other  is a text and DAX does not allow comparison betwen text and numbers.  @bidevsugmen's solution should work.

 

Alternatively, this formula  can also be written as:

 

balcon =
IF (
    r_info[info] = "balcon"
        || r_info[text] = ""
        || r_info[text] = "-"
        || r_info[text] = "k.A."
        || ISNUMBER ( SEARCH ( "keine*", r_info[text] ) ),
    "No",
    "Yes"
)

 

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
bidevsugmen
Resolver I
Resolver I

Hi @Anonymous

 

You can try out the below formula (it has some modifications in the formula you provided).

 

balcon = IF
(r_info[info] = "balcon" ||
r_info[text] = "" ||
r_info[text] = "-" ||
r_info[text] = "k.A." ||
SEARCH("keine", r_info[text],1,"other") >0,
"No",
"Yes")

 

The reason is > operator used in SEARCH function returns true or false, which is evaluated using IF.

This should resolve the error.

 

Regards,

Suguna.

Anonymous
Not applicable

Hi @bidevsugmen,

I'm getting this error now "Cannot convert value 'other' of type Text to type Integer."

Hi again

 

The error is because of the fourth parameter in the SEARCH function "other"

Its recommended to use 0 for that parameter value.

You can go through the below link to get more insights for the reason of the error.

https://msdn.microsoft.com/en-us/query-bi/dax/search-function-dax

 

Updated formula-

balcon = IF
(r_info[info] = "balcon" ||
r_info[text] = "" ||
r_info[text] = "-" ||
r_info[text] = "k.A." ||
SEARCH("keine", r_info[text],1,0) >0,
"No",
"Yes")

Anonymous
Not applicable

Hi @bidevsugmen Cool, thanks man.

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.