Forum Discussion
Using COUNTA with exceptions
Okay, I think this should work, hopefully lol.
Total Errors = CALCULATE(
COUNTA('Excel_GCRT (2)'[Error Code]) + COUNTA('Excel_GCRT (2)'[2nd Error code]) + COUNTA('Excel_GCRT (2)'[3rd Error code]),
FILTER(
ALL('Excel_GCRT (2)'),
NOT('Excel_GCRT (2)'[Error Code]) || NOT('Excel_GCRT (2)'[2nd Error code]) || NOT('Excel_GCRT (2)'[3rd Error code]) IN{"E0","E17"}
)
)
When you want to filter something in a DAX calculation, you specify the filtering in the CALCULATE code, like above. I've learned a lot about DAX from Curbal https://www.youtube.com/channel/UCJ7UhloHSA4wAqPzyi6TOkw and Entrerprise DNA https://www.youtube.com/channel/UCy2rBgj4M1tzK-urTZ28zcA. They have a lot of videos on how to utilize basically all of the DAX operators correctly. I suggest looking up some of their stuff, they're really great.
I hope this does what you need.
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.