# DAX Count with condition



## Gracebite

How to Count a column with condition using DAX function in Power Pivot

Example: I have to count a column A with condition value "Yes" (A="Yes") in that column


Thanks
Grace


----------



## Fahadfx

Hi,

You caould use Countrows with filter lets say that your column name is A and your table name is Table1 the dax will be =countrows(filter(table1,table1[A]="Yes"))

and lets say that you have more than value in your column A and you want to count each one then use = COUNTROWS(FILTER(Table1,Table1[A] =EARLIER(Table1[A])))

Hope this could help you


----------



## elliotstan

I have come across the same issue and this fix by Fahadfx helped. 
However, I need to filter out multiple values. I am trying to get a count of rows but not including multiple values. 

I have =COUNTROWS(FILTER(Table1,Table1[Column]<>"value to not count")) which workes fine. 
How do I add in more "values to not count"?

Cheers in advance. 
ED


----------



## elliotstan

Still playing around with this so thought I would come back and put down the answer I got. 

=CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Column]<>"value1 to not count"),Table1[Column]<>"value2 to not count",Table1[Column]<>"value3 to not count")   ...and so on. 
All comes from the filter expression. 
I found the answer on SQLBI - Marco Russo : How CALCULATE works in DAX
About half way down he gets to adding in extra filter conditions. Great site. 
ED.


----------



## Ozeroth

You could use CONTAINS(), as long as you have a table with a column of values to exclude with a column *ExcludeTable[ExcludeValue]*:



		Code:
__


=
COUNTROWS (
    FILTER (
        Table1,
        NOT (
            CONTAINS (
                ExcludeTable,
                ExcludeTable[ExcludeValue], Table1[Column]
            )
        )
    )
)


----------



## ARAbbott

This looks like it should be perfect for me, but when I enter it into the formula I get: "ERROR - CALCULATION ABORTED: MdxScript(Sandbox) (16, 103) Cannot find table 'Seven Challenges - individual'."  Ideas on why it'd be considering my filter criterion as a table entry?

Thanks,

Anna


----------



## ARAbbott

Nevermind, sorry folks.  Apparently my eyes are bad enough that I can't tell the difference between single and double quotes any more.


----------

