Count rows with conditions based on two columns.

[matrix_user]

Hello,

I am looking for help for a DAX expression (measure) that would allow me to count rows with the condition that it has a start date and no end date only. Based on the example bellow the DAX expression I am looking for should yield the result of 5 counts.

ID 001	Start date	End date
ID 002	12/05/2016	17/09/2016
ID 003	25/09/1990	12/07/1994
ID 004	2/04/2002	18/08/2003
ID 005	4/07/2014
ID 006	13/03/2022	20/04/2022
ID 007	11/06/2021
ID 008	18/12/2012
ID 009	12/04/2022
ID 010	1/02/2017
Total count  without End date = 5
Total count with End date = 4

Many thanks.

[tamerj1 · Accepted solution]

Hi @matrix_user 

please try

Count =
COUNTROWS (
    FILTER (
        'Table',
        NOT ISBLANK ( 'Table'[Start Date] ) && ISBLANK ( 'Table'[End Date] )
    )
)

[Jayee]

Hi Matrix_user

here the Measure formula for your query

Count with End Date = COUNTX('Table','Table'[End date])
Count without End Date = COUNTX('Table',IF(ISBLANK('Table'[End date]),1))