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||Last Necropsy Date||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||Last Necropsy Date||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:
Solved! Go to Solution.
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!
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.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.