Forum Discussion

jfinley1128's avatar
jfinley1128
Regular Visitor
9 years ago
Solved

Help with Measure for contains

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 f...
Need Help
Tutorial Requests
  • v-micsh-msft's avatar
    v-micsh-msft
    9 years ago

    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

v-micsh-msft's avatar
v-micsh-msft
Microsoft Employee
9 years ago

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's avatar
jfinley1128
Regular Visitor
9 years ago

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's avatar
    v-micsh-msft
    Microsoft Employee
    9 years ago

    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

    • jfinley1128's avatar
      jfinley1128
      Regular Visitor
      9 years ago

      This worked. Thankyou very much!

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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