Forum Discussion
Filter Dax Function doesn't work
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 ...
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." ) ) ))
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. .
|
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."
)
)
))Greg_Deckler the issue is fix. thanks for your support 🙂