Countif for dynamic criteria range

[amon151]

Hello!

I'm trying to replicate the excel COUNTIF function in DAX. I basically want to count the number of instances each value of column B appears in column A range.

Excel Formula

=COUNTIF($A:$A,B2)

I came across the DAX formula below, but it doesn't seem to be doing what I want or perhaps I'm implementing it incorrectly.

=CALCULATE(COUNT('Table'[Column A]),ALLEXCEPT('Table','Table'[Column B]))

Any help is greatly appreciated!!!

Thanks

[SpartaBI · Accepted solution]

@amon151  if a column then this is the code: 

Output = 
VAR _b = 'Table'[Column B]
VAR _result = 
COUNTROWS(
    FILTER(
        'Table',
        'Table'[Column A] = _b
    )
)
RETURN
    _result

If a measure then this:

Output Measure = 
VAR _b = SELECTEDVALUE('Table'[Column B])
VAR _result = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        'Table'[Column A] = _b
    )
)
RETURN
    COALESCE(_result, "")

      

Showcase Report – Contoso By SpartaBI

[amon151]

The solution you provided for the column is just what I needed!!

Thank you so much for the quick response 👍

[SpartaBI]

@amon151 my pleasure 🙂

Check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. 
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂

[amitchandak]

@amon151 , Check update from SpartaBI 

Try a new column

= countx(filter(Table, Table[ColumnA] = earlier(Table[Column B]) ), [ColumnA])+ 0

A new measure

= countx(filter(allselected(Table), Table[ColumnA] = max(Table[Column B]) ), [ColumnA])+ 0

[amon151]

Thank you!

This approach seemed to work for the most part but wasn't able to handle when Column B was blank (ignore the third column, the output of your formula is in column D).

[SpartaBI]

@amon151 you need this as a measure or as a calculate column?