Count blank end dates rows but not limited to slicer dates.

[matrix_user]

I have data with no end date and these are considered open events. Below is an example:

1) I have filtered this using a slicer from startdate 01/01/2022 to enddate 31/12/2022.

2) I am seeking a DAX measure to count rows of all rows with blank endates but no limited to the slicer's parameters.

In the above example, the DAX measurer I am seeking will yeild 3 counts of rows with no end dates (icd 001, icd 003 and icd 008) despite having start dates outside the slicer's paremeters.

3) The DAX measure will yeild counts for rows with both start dates and end dates but will not yield icd 002 or icd 010 because they do not fall within the slicer's parameters.

Kudos for your help.

[matrix_user]

I am still struggling with this... the end result I am trying to acheive is to count rows with start date BUT no end date AND not limited to slicer's start date of "01/01/2022"

[Karthikstark]

Try this 

AllDateswithBlank = CALCULATE(COUNTBLANK(DateTable[enddate]), ALL(DateTable[startdate]))

and for counting dates within the slice parameters:

 

CountRowsWithInSlicerParameters = COUNT(DateTable[enddate])

[matrix_user]

Hello,

If I use the ALL function where would I put it in the below Dax expression? This expression currently counts all the rows with no end date - but only within the slicer's parameters:

Cases open = 

VAR Enddatevisual = MAX ('Datetable' [Date])

VAR Startdatevisual = MIN ('Datetable' [Date])

VAR RSEULT = CALCULATE ([Total cases],

ISBLANK ('Table' [Enddate]))

RETURN

RESULT

Thank you,

[Anonymous · Accepted solution]

Hi @matrix_user ,

Slicer works:

Cases open = CALCULATE(COUNT('Table'[ID]),FILTER(ALL('Table'),'Table'[Startdate]<MAX(slicer[date])&&('Table'[Enddate]>MIN(slicer[date])||ISBLANK('Table'[Enddate]))))

Slicer does not work:

Cases open = CALCULATE(COUNT('Table'[ID]),FILTER(ALL('Table'),ISBLANK('Table' [Enddate])))

Best Regards,

Jay

[Anonymous]

Hi @matrix_user ,

You could use ALL() function to wrap the table and remove the filter while calculating.

https://learn.microsoft.com/en-us/dax/all-function-dax .

Best Regards,

Jay