Countif Contains Based on a List

[matthesc]

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 · Accepted solution]

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

[Interkoubess]

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