Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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:
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!
Thanks again.
Hope the following measure works
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
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!
Hope this helps.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
127 | |
108 | |
87 | |
70 | |
66 |