Help with Measure for contains

[jfinley1128]

I am using the Power BI Desktop App to build a report against an Azure storage table which contains error logging for our web services. I am trying to write a measure against the description column for contains to get a better idea of what errors are occurring by looking for specific key words, su7ch as address, phone, name, etc.

Error Message Examples:

Error publishing message to CRM: Phone Verification failure.  Phone number format is invalid or contains invalid characters.

Error publishing message to CRM: Validator error. Command: CreateContact - Description: ErrorCode: 12001 - (Details: Address state is missing).

Error publishing message to CRM: Validator error. Command: CreateContact - Description: ErrorCode: 12004 - (Details: First name is missing.)

I have tried the following:

Measure = CONTAINS(ALL([Description]), "Address")

This results in an error that “Too few arguments were passed to the CONTAINS function. The minimum argument count for the function is 3.”

I have also tried:

Measure = CONTAINS(AuditLog[Description], "Address", AuditLog[Description], "error", AuditLog[Description],"missing")

This results in an error that “the number of arguments is invalid. Function CONTAINS must have a value for each specified column reference.”

I have also tried a couple other variants and receive syntax errors. Could someone help me with getting a querry correct for this?

[v-micsh-msft]

Hi jfinley1128,

If you are using Contains function in DAX under Power BI desktop Data/Report View to filter special text from error messages, this can’t be done, at least with the Contains function.

CONTAINS Function (DAX)

Parameters

table Any DAX expression that returns a table of data.

columnName The name of an existing column, using standard DAX syntax. It cannot be an expression.

value Any DAX expression that returns a single scalar value, that is to be sought in columnName. The expression is to be evaluated exactly once and before it is passed to the argument list.

Contains function in DAX would find under the column that specified in the second syntax, with the value specified behind the column name.

The value should be a column value, not a part of it. So no matter how many syntax changed here, this function won’t work.

If we would like to search for the special text within the column, we could take use of the following formula, in a calculated column. This would create a reference column and mark the column to 1 if ‘table’[ColumnName] contains Text.

Value = iF(

               ISERROR(

                    SEARCH("Text",’table’[ColumnName])),

                 0,

                 1)

Reference:

SEARCH Function (DAX)

In your scenario, we could workaround the issue with the formula below, in a calculated column:

Errortype = iF(

               ISERROR(

                    SEARCH("Phone", AuditLog[Description])),

                    iF(

                     ISERROR(

                          SEARCH("Address", AuditLog[Description])),

                          iF(

                             ISERROR(

                             SEARCH("name", AuditLog[Description])),

                              “Null”,

                              “Name”),

                       “Address”),

                 “Phone”)

See my test result:

Adding another thread for reference:

If text column CONTAINS specified value, give me what I want

If any further help needed, please post back.

Regards

[jfinley1128]

Hi Michael,

Thanks for the assistance and providing a precise querry. I tried both pasting and typing in the querry and get the following error when I try to add the calculated column on the modeling tab: 

The following syntax error occurred during parsing: Invalid token, Line 1, Offset 155, “.

[v-micsh-msft · Accepted solution]

jfinley1128,

Remove the parts which are red underlined, then input them manually, this should be syntax symbol errors.

Errortype = iF(
               ISERROR(
                    SEARCH("Phone", AuditLog[Description])),
                    iF(
                     ISERROR(
                          SEARCH("Address", AuditLog[Description])),
                          iF(
                             ISERROR(
                             SEARCH("name", AuditLog[Description])),
                              "null",
                              "Name"),
                       "Address"),
                 "Phone")

Or copy and paste the code from here.

If any further assistance needed, please post back.

Regards

[Anonymous]

Hi,

Is it posible to search a string based on a measure?

 as SEARCH([Measure],Column Name)? I tried this one but it didnt work.

Regards,

Maddy

[jfinley1128]

This worked. Thankyou very much!