topic Re: Count blank end dates rows but not limited to slicer dates. in DAX Commands and Tips

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

matrix_user — Wed, 19 Oct 2022 14:52:12 GMT

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.

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

Anonymous — Thu, 20 Oct 2022 09:12:49 GMT

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

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

matrix_user — Sat, 22 Oct 2022 06:30:04 GMT

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,

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

Karthikstark — Sat, 22 Oct 2022 07:32:59 GMT

Try this

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

and for counting dates within the slice parameters:

CountRowsWithInSlicerParameters = COUNT(DateTable[enddate])

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

matrix_user — Sat, 22 Oct 2022 08:03:01 GMT

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"

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

Anonymous — Fri, 28 Oct 2022 05:42:56 GMT

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