cancel
Showing results for
Did you mean:
Highlighted
Post Partisan

## Using COUNTA with exceptions

Hi there,

I'm creating a measure which counts the total number of non-blank rows from three columns in my data set.

The columns contain an error code - there are three columns so if there are multiple errors, people can enter up to three.

So the 'Total Errors' measure just sums all the codes from all three columns up. Nice.

But there are two codes which mean 'No Error' = E0 and E17

So I want to include an additional argument to my measure to say count the error codes except if they =E0 or E17.

Any ideas????

Jemma

7 REPLIES 7
Highlighted

## Re: Using COUNTA with exceptions

Not sure how you are calculating it with your measure but I just figured out how to not include things in my calculations.

FILTER(

ALL(Table),

NOT(Table[Column]) IN {"E0","E17"}
)

It basically reads like, "Everything in the column that's not like E0 or E17".  Give that a try and let see if it works.

Highlighted
Post Partisan

## Re: Using COUNTA with exceptions

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])IN{"E0","E17"})

Hi there Drewdel,

Thanks for the reply. I tried to replicate your code into my DAX and it's getting upset - I think i've done it wrong.

There are three columns that it's counting: 'Error Code', '2nd Error code' and '3rd Error code' and the COUNTA formulae is working fine. It's bringing back what I would expect.

Now I want to it to count the same, but exclude E0 and E17 from all three columns. I figure if I can get the formula right for one column I can just add for the other two columns at the end... unless I need to imbed the filter within the COUNTA?

I've included my DAX above so you can see what i'm trying to do.

Highlighted

## Re: 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.

Highlighted
Post Partisan

## Re: 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 🙂

Highlighted

## Re: Using COUNTA with exceptions

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"})
)
)

Highlighted
Post Partisan

## Re: Using COUNTA with exceptions

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 🙂

Highlighted

## Re: Using COUNTA with exceptions

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021