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

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