Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
JLambs20
Helper III
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.1PhaseStageStartEndSDGroup In-Life
123456Receipt / TransferPre-Treatment27-Sep-2204-Oct-22SD1In-Life
123456Treatment StartPre-Treatment27-Sep-2204-Oct-22SD1In-Life
123456Treatment EndTreatment05-Oct-2211-Jan-23SD1In-Life
123456Treatment StartTreatment05-Oct-2211-Jan-23SD1In-Life
123456Last Necropsy DateRecovery12-Jan-2322-Feb-23SD1In-Life
123456Treatment EndRecovery12-Jan-2322-Feb-23SD1In-Life
987654Receipt / TransferPre-Treatment17-Jan-2323-Jan-23SD1In-Life
987654Treatment StartPre-Treatment17-Jan-2323-Jan-23SD1In-Life
987654Treatment EndTreatment24-Jan-2301-Feb-23SD1In-Life
987654Treatment StartTreatment24-Jan-2301-Feb-23SD1In-Life
987654Last Necropsy DateRecovery02-Feb-2303-Feb-23SD1In-Life
987654Treatment EndRecovery02-Feb-2303-Feb-23SD1In-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
Padycosmos
Solution Sage
Solution Sage

Hope this helps.

Padycosmos_1-1674074995839.png

 

 

 

View solution in original post

6 REPLIES 6
JLambs20
Helper III
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.

Hope the following measure works

Padycosmos_0-1674498797033.png

 

JLambs20
Helper III
Helper III

@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. 

You are welcome. I hopethe following measure would help

Padycosmos_1-1674265283772.png

 

 

JLambs20
Helper III
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!

Padycosmos
Solution Sage
Solution Sage

Hope this helps.

Padycosmos_1-1674074995839.png

 

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.