Forum Discussion

matthesc's avatar
matthesc
New Member
9 years ago
Solved

Countif Contains Based on a List

Hello,

 

I am trying to create a measure that will count the number of instances a particular phrase appears in a column based on a unique ID. Example, I have a list of customers that has a column titled 'Pets' and in this columns the data is structured as '{Cat},{Dog},{Lizard},{Bird}' for customer 1, and customer 2 has '{Cat},{Fish}', customer 3 has '{Dog},{Bird}'. Based on the Pets column I would like to know the count of customers who own a Cat. Im gussing the function would be similar to the Countif function in excel?

 

Thanks

  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi matthesc,

     

    You can add a custom column and use 'Text.Split' and 'List.Contains' function to check if it contains specific items.

     

    Sample formula:

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Contains", each List.Contains(Text.Split([Pet],","),"{Cat}"))

     

    You can use below function to replace bold part of formula to change the check method or check item amount.

     

    Reference link:

    Function Description
    List.Contains Returns true if a value is found in a list.
    List.ContainsAll Returns true if all items in values are found in a list.
    List.ContainsAny Returns true if any item in values is found in a list.
    Text.Split Returns a list containing parts of a text value that are delimited by a separator text value.

     

     

    Regards,

    Xiaoxin Sheng

2 Replies

  • Interkoubess's avatar
    Interkoubess
    Solution Sage
    9 years ago

    Hi,

     

    Suppose your client column is named Customer, cretae this measure CustomerNumber=Distinctcount([Customers]) and put in your report for example the pets colums in row and then your measure.

     

    Thx

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi matthesc,

     

    You can add a custom column and use 'Text.Split' and 'List.Contains' function to check if it contains specific items.

     

    Sample formula:

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Contains", each List.Contains(Text.Split([Pet],","),"{Cat}"))

     

    You can use below function to replace bold part of formula to change the check method or check item amount.

     

    Reference link:

    Function Description
    List.Contains Returns true if a value is found in a list.
    List.ContainsAll Returns true if all items in values are found in a list.
    List.ContainsAny Returns true if any item in values is found in a list.
    Text.Split Returns a list containing parts of a text value that are delimited by a separator text value.

     

     

    Regards,

    Xiaoxin Sheng