Count formula: How to exclude values from count based on text

[LABrowne]

Hi there,

Currently have the below formula.

CALCULATE (
COUNT ( Table[Column] ),
filter('table',[SpicyLevel (%)] >1 &&[No. of Orders] >= 2 &&
TODAY () <= [Expiry Date Less 90 Days])
)

This works however I need to add another condition.

Orders that are cancelled will have "Cancelled" next to the order number. How to I add another condition to exclude text containing "Cancelled" within the Order[OrderNumber]?

Thanks,

Luke

[ValtteriN]

Hi,

If I understood you problem you have data like this:

You can add yet another condition with "&&"

As for the condition use functions like CONTAINSSTRING

Count not cancelled =

COUNTROWS(FILTER('Table (13)',

    NOT(CONTAINSSTRING('Table (13)'[OrderNumber],"Cancelled"))))

End result:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Amerluzz]

Might not be the best way, but in PQE you can add a conditional column with a "Contains" clause. If Order Number contains "Cancelled" --> "Cancelled",  else --> "Active".

Then calculate your measure where your new column = "Active"