Calculating a measure based on finding text in a column

[Anonymous]

Hi,

I'm looking for some advice on how I can create a measure that counts the number of customers that do not have a specific event recorded against them.  An example of this is:

• I have a Customers table
• I have a Sales Fact table joined to the Customers (I do a DISTINCT on Customer Key to calculate the number of customers)
• I have a Customer Note table connected to the Customer table

I would like to create a calculation which searches for specific text in the Customer Notes table and then filters the Customer Count measure to include Customers that don't have the search string in a Customer Note.

I've tried doing it the other way round first, calcualting the customer count that does have a specific text in a note, but I'm not having much luck:

Missing Fee Note =
CALCULATE(
[Customer Count],
FILTER ( Customer, CONTAINSSTRING ( 'Customer Note'[Note Text], "search text" ) )

I get an error above which says that it can't determine a single value.  Any help or pointers would be appreicated.

Thanks

[v-yinliw-msft]

Hi @Anonymous ,

You can try the following methods.

Here's my test table:

Customer Note Table:

Customer Table:

Sales Fact Table:

Measure:

Missing Fee Note =

CALCULATE (

    [Customer Count],

    FILTER (

        ALL ( 'Customer Note' ),

        CONTAINSSTRING ( 'Customer Note'[Note Text], "fail" )

    )

)

In your code, the first argument to the filter function should probably be used Customer Note.

Missing Fee Note =
CALCULATE(
[Customer Count],
FILTER ( Customer, CONTAINSSTRING ( 'Customer Note'[Note Text], "search text" ) )

Is this the result you expect?

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for the example.  I was looking for instances where the text was not contained within the string.

[amitchandak · Accepted solution]

@Anonymous , make sure Customer Count is a measure

Try like example

CALCULATE(
count(Sales[Customer ID]) ,
FILTER ( Customer, not CONTAINSSTRING ( 'Customer Note'[Note Text], "search text" ) ) //added not , remove as per need