Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago

countif column has entry (don't count blanks)

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 wit...
Anonymous's avatar
Anonymous
Not applicable
10 years ago

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's avatar
Sean
Icon for Community Champion rankCommunity Champion
10 years ago

What numbers do you get from each of these

 

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

 

and what DATA TYPE is the column?

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    Got it working

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

    • Sean's avatar
      Sean
      Icon for Community Champion rankCommunity Champion
      10 years ago
      I must not be understanding this question???
      • ImkeF's avatar
        ImkeF
        Icon for Community Champion rankCommunity Champion
        10 years ago

        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.