Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 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
Solved! Go to Solution.
@DavidC2020 , 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
Hi @DavidC2020 ,
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.
Thank you for the example. I was looking for instances where the text was not contained within the string.
@DavidC2020 , 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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
12 | |
11 | |
10 | |
9 |