Forum Discussion

matrix_user's avatar
matrix_user
Icon for Helper III rankHelper III
3 years ago
Solved

Count rows with conditions based on two columns.

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 001Start dateEnd date
ID 00212/05/201617/09/2016
ID 00325/09/199012/07/1994
ID 0042/04/200218/08/2003
ID 0054/07/2014 
ID 00613/03/202220/04/2022
ID 00711/06/2021 
ID 00818/12/2012 
ID 00912/04/2022 
ID 0101/02/2017 
   
Total count  without End date = 5
Total count with End date = 4

 

Many thanks.

 

  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi matrix_user 

    please try

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

2 Replies

  • Jayee's avatar
    Jayee
    Icon for Responsive Resident rankResponsive Resident
    3 years ago

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

     

     

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi matrix_user 

    please try

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