Forum Discussion
IF / COUNT IF
Hi, I have this formula where I filter for several conditions.
1- The first conditions search in the columns if any data contains the word (DEFAULT, MASIVO, etc) in part of its text and they count if the data found are = 0.
COUNTIF([@[ID SERVICIO]],"*DEFAULT*")=0,COUNTIF([@[ID SERVICIO]],"*MASIVO*")=0,COUNTIF([@[ID SERVICIO]],"*ESC_HN_DEF001*")=0
Total Table for example:
Filter:
2- The second condition is if "ID SERVICIO" is different from:
[@[ID SERVICIO]]<>"0",
[@[ID SERVICIO]]<>0,
[@[ID SERVICIO]]<>" 0",
[@[ID SERVICIO]]<>"",
3- The other conditions are:
[@FEMTOCELDA]="NO FEMTOCELDA",
[@CATEGORÍA]<>"DX - AFECTACION MASIVA",
[@[TIPO_SERVICIO]]<>"NO_CMDB")
If all the above conditions are correct, "VALIDO" otherwise "NO VALIDO".
----------------------------------------------------------------------------------------------------------------------------------------------
I try to emulate the formula but it tells me the following:
On the other hand, I do not know if the way to search words "DEFAULT", "MASIVO", etc, is correct in the DAX syntax that I use.
I appreciate your comments and guide.
As the error message mentioned, the format of some columns like [@[ID SERVICIO]] is not number type, so they can not be used in comparision with 0, you should change the type from text to number or use value() function to convert them.
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You may use DAX function like FIND, SEARCH, PositionOf and Contains, you may refer to the text function in link below:
https://docs.microsoft.com/en-us/dax/find-function-dax
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
As the error message mentioned, the format of some columns like [@[ID SERVICIO]] is not number type, so they can not be used in comparision with 0, you should change the type from text to number or use value() function to convert them.
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, perfect, thank you very much, I will review, on the other hand, ¿I'm using well the way to find part of the text and Count the data?
Example of correct names:
DEFAULT_HONDURAS,
ENLACE_DEFAULT_SV
MASIVO_HN
EQUIPO_MASIVO_SV
SV_ESC_HN_DEF001_HN
EXCEL:
=IF(AND(COUNTIF([@[ID SERVICIO]];"*DEFAULT*")=0;COUNTIF([@[ID SERVICIO]];"*MASIVO*")=0;COUNTIF([@[ID SERVICIO]];"*ESC_HN_DEF001*")=0;"VALIDO";
DAX:
=SWITCH(TRUE();CALCULATE(COUNT(TICKETS[ID SERVICIO]);TICKETS[ID SERVICIO]="*DEFAULT*")=0 && CALCULATE(COUNT(TICKETS[ID SERVICIO]);TICKETS[ID SERVICIO]="*MASIVO*")=0 && CALCULATE(COUNT(TICKETS[ID SERVICIO]);TICKETS[ID SERVICIO]="*ESC_HN_DEF001*")=0;"VALIDO";What is the proper form or formula in DAX to search for words within a text?Thanks.You may use DAX function like FIND, SEARCH, PositionOf and Contains, you may refer to the text function in link below:
https://docs.microsoft.com/en-us/dax/find-function-dax
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
