countif column has entry (don't count blanks)

[Anonymous]

Hi,

I need to be able to count the number of entries in a data column and not count the blanks.

So for example under a column labelled "tag 1" there maybe 25,000 rows but only 150 entries with the other 24,850 rows left blank. I want the count to return 150.

Thanks,

[g0bk1]

This is an old question, so you may try DISTINCTCOUNTNOBLANK(Table[column]) now

[Sean]

@Anonymous Try COUNTA( )

Here are the other counting functions...

https://msdn.microsoft.com/en-us/library/ee634956.aspx

[Anonymous]

Already tried COUNTA and it counts all the rows.

[Greg_Deckler]

@Anonymous, @Sean is correct that if the data in those rows truly was empty that COUNTA should count them the way you describe and COUNTBLANK can be used to determine if they truly are blank.

In the event that there is something in them, like " " or something, the general format would be:

MyMeasure = CALCULATE(COUNT([NumericColumn]),FILTER(SomeTable[SomeColumn] <> ""))

The above formula is essentially the equivalent of COUNTA. The advantage here is that if it is truly not empty (blank) then you can adjust the "" in the formula to something like " ".

Hope you get to the bottom of it.

[Anonymous]

Thanks but that formula is not working and potentially I'm doing it wrong

Airport Line = CALCULATE(count('EngagorData'[Tag-L3 » Airport Line]),FILTER('EngagorData','EngagorData'[Tag-L3 » Airport Line]<>""))

Above is the formula I used and it returns nothing.

In your formula you reference to "numericcolumn and sometable some column. am i missing something.

My data table is call "EngagorData" and the column is called "Tag-L3 » Airport Line". In each cell it is either blank or has the entry "Tag-L3 » Airport Line"

I have even tried this and returns nothing.

Airport Line = CALCULATE(count('EngagorData'[Tag-L3 » Airport Line]),FILTER('EngagorData','EngagorData'[Tag-L3 » Airport Line]="Tag-L3 » Airport Line"))

[Sean]

What numbers do you get from each of these

COUNTROWS( ), COUNTA( ), COUNT( ) and COUNTBLANK( )

and what DATA TYPE is the column?

[Anonymous]

Got it working

Measure = COUNTA('EngagorData'[Tag-L3 » Airport Line]) - COUNTBLANK('EngagorData'[Tag-L3 » Airport Line])

[Sean]

I must not be understanding this question???

[ImkeF]

I like to recommend that you use the query editor to clean your tables before loading them into the data model.

If a blank that isn't blank creates problems already at this stage, there's quite a likelihood that it will produce other errors as well later on - maybe some of them that will not as easily be spotted as this one.

[Sean]

Okay - so what does COUNTBLANK( )give you then? You are creating Measures right?