topic Count rows with conditions based on two columns. in DAX Commands and Tips

Count rows with conditions based on two columns.

matrix_user — Wed, 19 Oct 2022 03:47:13 GMT

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.

Re: Count rows with conditions based on two columns.

Jayee — Wed, 19 Oct 2022 03:59:10 GMT

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))

Re: Count rows with conditions based on two columns.

tamerj1 — Wed, 19 Oct 2022 04:03:28 GMT

Hi matrix_user

please try

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