Forum Discussion

LABrowne's avatar
LABrowne
Icon for Helper II rankHelper II
2 years ago

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

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

2 Replies

  • Amerluzz's avatar
    Amerluzz
    New Member
    2 years ago

    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" 

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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/