Forum Discussion
Countif for dynamic criteria range
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.
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 _resultIf 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, "")
6 Replies
- amitchandak
Super User
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
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
Community Champion
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 _resultIf 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, "")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 🙂
