Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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 :-)