Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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")
Solved! Go to 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")
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" )
Proud to be a Super User!
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.
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")
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |