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

 

Need Help

7 Replies

  • 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.