Filter Dax Function doesn't work

[Anonymous]

I would like to count rows only when "Number of Days to Recover" column is"Job with no recovery plan" and Job Notes column contains spefici word. Below are my Dax function but it doesn't work. Those rows that doesn't contain specific word still being count. Please advise what wrong with my formula? 

Total Number of Job with No Recovery Plan =

COUNTROWS(

    FILTER(

        'Service Success Report',

        (

             'Service Success Report'[Number of Days to Recover] = "Job with no recovery plan" &&

                (countBLANK('Service Success Report'[Job Notes]) ||

                CONTAINSSTRING('Service Success Report'[Job Notes], "did not attend") ||

                CONTAINSSTRING('Service Success Report'[Job Notes], "didn't attend") ||

                CONTAINSSTRING('Service Success Report'[Job Notes], "could not attend") ||

                CONTAINSSTRING('Service Success Report'[Job Notes], "can't attend") ||

                CONTAINSSTRING('Service Success Report'[Job Notes], "unable to attend") ||

                CONTAINSSTRING('Service Success Report'[Job Notes], "unabel to attend") ||

                CONTAINSSTRING('Service Success Report'[Job Notes], "outside access time") ||

                CONTAINSSTRING('Service Success Report'[Job Notes], "no access") ||

                CONTAINSSTRING('Service Success Report'[Job Notes], "miss") ||

                CONTAINSSTRING('Service Success Report'[Job Notes], "missed") ||

                CONTAINSSTRING('Service Success Report'[Job Notes], "re-planned") ||

                CONTAINSSTRING('Service Success Report'[Job Notes], "replanned") ||

                CONTAINSSTRING('Service Success Report'[Job Notes], "postponed") ||

                CONTAINSSTRING('Service Success Report'[Job Notes], "did not occur") ||

                CONTAINSSTRING('Service Success Report'[Job Notes], "didn't occur") ||

                CONTAINSSTRING('Service Success Report'[Job Notes], "didn't take place") ||

                CONTAINSSTRING('Service Success Report'[Job Notes], "did not take place") ||

                CONTAINSSTRING('Service Success Report'[Job Notes], "unable to re-attend") ||

                CONTAINSSTRING('Service Success Report'[Job Notes], "run incomplete") ||

                CONTAINSSTRING('Service Success Report'[Job Notes], "run incompleted") ||

                'Service Success Report'[Job Notes] = "Cancellation reason: cancelled ." ||

                'Service Success Report'[Job Notes] = "Cancellation reason: cancelled." ||

                'Service Success Report'[Job Notes] = "Cancellation reason: cancel ." ||

                'Service Success Report'[Job Notes] = "Cancellation reason: canceleld." ||

                'Service Success Report'[Job Notes] = "Cancellation reason: canceleld ." ||

                'Service Success Report'[Job Notes] = "Cancellation reason: cancel lift." ||

                'Service Success Report'[Job Notes] = "Cancellation reason: cancel job ." ||

                'Service Success Report'[Job Notes] = "Cancellation reason: canceleld " ||

                'Service Success Report'[Job Notes] = "Cancellation reason: cancelled. ." ||

                'Service Success Report'[Job Notes] = "Cancellation reason: canvclled ." ||

                'Service Success Report'[Job Notes] = "Cancellation reason: Canceled." ||

                'Service Success Report'[Job Notes] = "Cancellation reason: cancel down." ||

                'Service Success Report'[Job Notes] = "Cancellation reason: Cancellation." ||

                'Service Success Report'[Job Notes] = "Cancellation reason: cancel off."

        )

    )

))

[Anonymous]

Hi Greg,

Please find below sample data. Highlight rows are the rows i expected to be counted and ignore the rest. However, Power BI still count everything 

[Greg_Deckler]

@Anonymous If you could provide that as text I can try to work out a solution for it or at least test your code and figure out what is going wrong.

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

Hi Greg,

Sure, please refer to below table:

Original Date	Completion Date	Number of Days to Recover	Job Notes
06/10/2023		Job with no recovery plan	Cancellation reason: site closed for holiday.
06/10/2023		Job with no recovery plan	Cancellation reason: Hey, Site is on holiday from tomorrow, Wednesday 04/10 and will resume collections on Friday 13/10. Can you please confirm this has been actioned. Warm regards, Hayleigh .
02/10/2023		Job with no recovery plan	Cancellation reason: last friday of the month.
04/10/2023		Job with no recovery plan	Cancellation reason: site on hoiday .
07/10/2023		Job with no recovery plan	Cancellation reason: canceleld as per email from Hayleigh.
07/10/2023		Job with no recovery plan	Cancellation reason: Hey, Site is on holiday from tomorrow, Wednesday 04/10 and will resume collections on Friday 13/10. Can you please confirm this has been actioned. Warm regards, Hayleigh .
03/10/2023		Job with no recovery plan	Cancellation reason: cancelled as per email from Katsu Coffee.
03/10/2023		Job with no recovery plan	Cancellation reason: Site closing from 12th September and reopening on 9th October.
04/10/2023		Job with no recovery plan	Cancellation reason: site closed for holiday.
04/10/2023		Job with no recovery plan	Cancellation reason: Site closing from 12th September and reopening on 9th October.
08/10/2023		Job with no recovery plan	Cancellation reason: Hey, Site is on holiday from tomorrow, Wednesday 04/10 and will resume collections on Friday 13/10. Can you please confirm this has been actioned. Warm regards, Hayleigh .
03/10/2023		Job with no recovery plan	Cancellation reason: cancelled .
06/10/2023		Job with no recovery plan	Cancellation reason: Site closing from 12th September and reopening on 9th October.
02/10/2023		Job with no recovery plan	Cancellation reason: Site closing from 12th September and reopening on 9th October.
02/10/2023		Job with no recovery plan
05/10/2023		Job with no recovery plan	Cancellation reason: Site closing from 12th September and reopening on 9th October.
05/10/2023		Job with no recovery plan	Cancellation reason: 4 bins required to be uplifted. .

[Greg_Deckler · Accepted solution]

@Anonymous Try this:

COUNTROWS(
    FILTER(
        'Service Success Report',
        (
             'Service Success Report'[Number of Days to Recover] = "Job with no recovery plan" &&
                ('Service Success Report'[Job Notes] = BLANK() ||
                CONTAINSSTRING('Service Success Report'[Job Notes], "did not attend") ||
                CONTAINSSTRING('Service Success Report'[Job Notes], "didn't attend") ||
                CONTAINSSTRING('Service Success Report'[Job Notes], "could not attend") ||
                CONTAINSSTRING('Service Success Report'[Job Notes], "can't attend") ||
                CONTAINSSTRING('Service Success Report'[Job Notes], "unable to attend") ||
                CONTAINSSTRING('Service Success Report'[Job Notes], "unabel to attend") ||
                CONTAINSSTRING('Service Success Report'[Job Notes], "outside access time") ||
                CONTAINSSTRING('Service Success Report'[Job Notes], "no access") ||
                CONTAINSSTRING('Service Success Report'[Job Notes], "miss") ||
                CONTAINSSTRING('Service Success Report'[Job Notes], "missed") ||
                CONTAINSSTRING('Service Success Report'[Job Notes], "re-planned") ||
                CONTAINSSTRING('Service Success Report'[Job Notes], "replanned") ||
                CONTAINSSTRING('Service Success Report'[Job Notes], "postponed") ||
                CONTAINSSTRING('Service Success Report'[Job Notes], "did not occur") ||
                CONTAINSSTRING('Service Success Report'[Job Notes], "didn't occur") ||
                CONTAINSSTRING('Service Success Report'[Job Notes], "didn't take place") ||
                CONTAINSSTRING('Service Success Report'[Job Notes], "did not take place") ||
                CONTAINSSTRING('Service Success Report'[Job Notes], "unable to re-attend") ||
                CONTAINSSTRING('Service Success Report'[Job Notes], "run incomplete") ||
                CONTAINSSTRING('Service Success Report'[Job Notes], "run incompleted") ||
                'Service Success Report'[Job Notes] = "Cancellation reason: cancelled ." ||
                'Service Success Report'[Job Notes] = "Cancellation reason: cancelled." ||
                'Service Success Report'[Job Notes] = "Cancellation reason: cancel ." ||
                'Service Success Report'[Job Notes] = "Cancellation reason: canceleld." ||
                'Service Success Report'[Job Notes] = "Cancellation reason: canceleld ." ||
                'Service Success Report'[Job Notes] = "Cancellation reason: cancel lift." ||
                'Service Success Report'[Job Notes] = "Cancellation reason: cancel job ." ||
                'Service Success Report'[Job Notes] = "Cancellation reason: canceleld " ||
                'Service Success Report'[Job Notes] = "Cancellation reason: cancelled. ." ||
                'Service Success Report'[Job Notes] = "Cancellation reason: canvclled ." ||
                'Service Success Report'[Job Notes] = "Cancellation reason: Canceled." ||
                'Service Success Report'[Job Notes] = "Cancellation reason: cancel down." ||
                'Service Success Report'[Job Notes] = "Cancellation reason: Cancellation." ||
                'Service Success Report'[Job Notes] = "Cancellation reason: cancel off."
        )
    )
))

[Anonymous]

@Greg_Deckler the issue is fix. thanks for your support 🙂

[Greg_Deckler]

@Anonymous Can you provide sample data?