Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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.

Anonymous
Not applicable

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. 

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.

Anonymous
Not applicable

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

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.