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 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 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." ) ) ))
6 Replies
Anonymous Can you provide sample data?
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.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. .