Forum Discussion
Using COUNTA with exceptions
Hi Drewdel,
You've been amazing. I'm going to check out those links as well! The code works, but i'm now getting saying an error message saying "Calulcation error message 'Excel)GCRT(2)'[Total Errors]: Cannot convert value 'E9' of type Text to type True/False."
I took out the filter formulae for 2nd Error and 3rd Error and the Total Errors is now just a row count. I can't even seem to create a slicer just for this one matrix as I could probably manually remove the unnecessary codes!
Thanks for your help on this :-)
I created a mock file to try and recreate what you're trying to do got this code to work. I put the IN {"E0","E17"} inside the NOT which made it calculate for each column separatly, so I hope this works for you as it did for me.
Total Errors =
CALCULATE(
COUNTA('Excel_GVRT (2)'[Error Code]) + COUNTA('Excel_GVRT (2)'[2nd Error Code]) + COUNTA('Excel_GVRT (2)'[3rd Error Code])
,FILTER(
ALL('Excel_GVRT (2)')
,NOT('Excel_GVRT (2)'[Error Code] IN {"E0","E17"}) || NOT('Excel_GVRT (2)'[2nd Error Code] IN {"E0","E17"}) || NOT('Excel_GVRT (2)'[3rd Error Code] IN {"E0","E17"})
)
)
Ok it's working, but it's just counting all the errors. The total error count is 9223 and whatever I include as a row e.g. Region, or Team, every line says 9223.
Do you have an email address I can send you a screenshot or share my report with you?
Annoyingly I could do this on Excel but i'm trying not to use it for reporting!
Jemma :-)
You can actually upload photos or files in these forum messages but if don't want to post your personal stuff like I understand and can do it through messaging if you like.