Solved: Re: Count Unique Values in a Date Range

JLambs20 · ‎01-18-2023

Hello!

I am trying to write a measure that will count the unique values in a column (the value can show up many times in the column in question). Each row also has a start and end date but my visual needs to show a count of all rows that exist within the selected date range, not just whether or not they start or end in the selected date range. Here's a sample of my data:

Study No.1	Phase	Stage	Start	End	SD	Group In-Life
123456	Receipt / Transfer	Pre-Treatment	27-Sep-22	04-Oct-22	SD1	In-Life
123456	Treatment Start	Pre-Treatment	27-Sep-22	04-Oct-22	SD1	In-Life
123456	Treatment End	Treatment	05-Oct-22	11-Jan-23	SD1	In-Life
123456	Treatment Start	Treatment	05-Oct-22	11-Jan-23	SD1	In-Life
123456	Last Necropsy Date	Recovery	12-Jan-23	22-Feb-23	SD1	In-Life
123456	Treatment End	Recovery	12-Jan-23	22-Feb-23	SD1	In-Life
987654	Receipt / Transfer	Pre-Treatment	17-Jan-23	23-Jan-23	SD1	In-Life
987654	Treatment Start	Pre-Treatment	17-Jan-23	23-Jan-23	SD1	In-Life
987654	Treatment End	Treatment	24-Jan-23	01-Feb-23	SD1	In-Life
987654	Treatment Start	Treatment	24-Jan-23	01-Feb-23	SD1	In-Life
987654	Last Necropsy Date	Recovery	02-Feb-23	03-Feb-23	SD1	In-Life
987654	Treatment End	Recovery	02-Feb-23	03-Feb-23	SD1	In-Life

For each item in the first column, there is a subsequent earliest date and last date for the item. "Stage" indicates what stage the item is in and the subsequent date range. If any any stage of the study would exist within the user's selected date range, then the measure should count that study. For example, if the user selected the month of February, the measure would return "2" because for each study, there is at least one stage that exists in February.

I have tried the following measure but I think it is counting each stage as one item as opposed to all of the stages being part of the same study:

Count Items Test =

COUNTX (

FILTER (

'All In-Life',

'All In-Life'[Start] <= MAX ( 'Calendar'[Date] )

&& (

ISBLANK ( 'All In-Life'[End] )

|| 'All In-Life'[End] >= MIN ( 'Calendar'[Date] )

)

),

'All In-Life'[Study No.1]

)

Any help would be greatly appreciated!

Thank you

Padycosmos · ‎01-18-2023

Hope this helps.

View solution in original post

JLambs20 · ‎01-23-2023

Thank you for your response here. This isn't quite what I was looking for. If I'm reading that correct, is that the number of studies that have a start AND an end date occurring between 01-JAN-2023 and 28-FEB-2023?

My current task is to figure out the number of studies with a Phase of "Pre-Treatment" and a start date within the selected date range. So, if the user selects the month of January in the date filter, then by looking at your table above, the count would be 2 because there are 2 studies with a Stage of "Pre-Treatment" and a start date in January. It doesnt' matter when that phase ends because all we are interested in is if the "Receipt / Transfer" phase starts in the selected date range.

Hopefully that all makes sense!

Thanks again.

Padycosmos · ‎01-23-2023

Hope the following measure works

JLambs20 · ‎01-20-2023

@Padycosmos

Hello again!

First off, thank you again for the solution to the previous issue. It's working perfectly. However, I need to expand on this just a bit and I'm having some trouble with the DAX.

Looking at the dataset you listed above, I now need to count the number of items that have Pre-Treatment start date within the date range that is selected (the date in the slicer on my page is the date column from my calendar table). For example, if I were to select the entire month of January as my date range, I'd see a count of 1 because it's the only project with a start date in January and is listed as "Pre-Treatment". Can this be worked into the current DAX statment or would we need a new one for the calculation?
Thank you in advance.