cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Partisan
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
Advocate II
Advocate II

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.

Add a filter that like this in your measure:

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
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
Advocate II
Advocate II

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
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
Advocate II
Advocate II

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
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
Advocate II
Advocate II

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

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

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors