cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## Count Unique Values in a Date Range

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
1 ACCEPTED SOLUTION
Solution Sage

Hope this helps.

6 REPLIES 6
Helper III

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.

Solution Sage

Hope the following measure works

Helper III

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.

Solution Sage

You are welcome. I hopethe following measure would help

Helper III

Yes! that worked wonderfully! It looks like maybe I was overthinking it as I use Calculate quite a bit.  Anyway, thank you so much for your help!

Solution Sage

Hope this helps.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors