Countrows using DAX

[Anonymous]

Hi

I am trying to count or tally up number of rows in a table but ignore any blank values.  The table is called in this example Projects and want to count the number of rows that have a date and ignore any null or blanks.  My matrix or pivot uses figures from a  table called AwardCounter.  So for example in Jan 2020 the measure brings up all the dates including any null values, so instead of a count = 13 I get count = 18. Is this because my relationship to each table, how the filters are flowing.  My filter context is coming from or is based on the AwardCounter, this populates the pivot or matrix Year and Monthname as simple stacked chart.     My measure or latest attempt looks like this - 

=CALCULATE(
                    COUNT('Projects'[Proj_No]),
                                  'Projects'[SbmitDate]<> BLANK()

                        )

Diagram View:

Diagram View

Any help would be great, many thanks

Chris

[amitchandak · Accepted solution]

@Anonymous ,

If you need blank
=CALCULATE(COUNTA('Projects'[Proj_No]) )
or
=CALCULATE(countrows('Projects'[Proj_No]) )

If you do need blank
=CALCULATE(COUNT('Projects'[Proj_No]),not(isblank('Projects'[SbmitDate])))

[AntrikshSharma · Accepted solution]

try using KEEPFILTERS, since 'Projects'[SbmitDate] <> BLANK () expands internally to

= FILTER ( ALL ( 'Projects'[SbmitDate] ), 'Projects'[SbmitDate] <> BLANK () )

 at each month you get the same list of dates

=
CALCULATE (
    COUNT ( 'Projects'[Proj_No] ),
    KEEPFILTERS ( 'Projects'[SbmitDate] <> BLANK () )
)

 but using KEEPFILTERS you can avoid overriding the existing filters and KEEPFILTERS adds the new filter to the exiting filter

[Anonymous]

Many thanks to all, very useful answers.

Chris

[amitchandak · Accepted solution]

@Anonymous ,

If you need blank
=CALCULATE(COUNTA('Projects'[Proj_No]) )
or
=CALCULATE(countrows('Projects'[Proj_No]) )

If you do need blank
=CALCULATE(COUNT('Projects'[Proj_No]),not(isblank('Projects'[SbmitDate])))

[lbendlin]

You probably want to use COUNTA() or COUNTAX() instead.