So, I'm trying to figure out if a table of Activities with corresponding date ranges (i.e. start and end dates) have any dates which overlap with a selection from a Dates slicer. I'm not sure how to relate the Dates table to the Activities. As an illustration, these are the Activities:
|Activity||Start Date||End Date|
If I were to use my slicer like so:
then I should get the following Activities:
This is because each of these Activities share occurrence dates with the slicer dates.
Ultimately, I'm trying to create a measure, ActivitiesCount, that can count the rows of this resulting set of Activities. For this example, ActivitiesCount would equal five (5).
Can you help me please? Thanks in advance!
Solved! Go to Solution.
1. Place Table1[Activity] in the rows of a visual table selecting 'Don't summarize'
2. Create this measure
ShowMeasure = IF ( MAX ( SlicerTable[SlicerColumn] ) < SELECTEDVALUE ( Table1[Start Date] ) || MIN ( SlicerTable[SlicerColumn] ) > SELECTEDVALUE ( Table1[End Date] ), 0, 1 )
3. Place [ShowMeasure] in the visual level filters of the table visual and select 'Showw items when the value' is --> 1
Thanks, @AlB. That's pretty clever but what I'm really trying to achieve is a measure which can count all the Activities with dates occurring within the slicer date range. For the example I gave initially, my measure would return five (5) activities.
Is there a way that this can be achieved?
The Activity count should be 6 (not 5). Activity G should also be included. You may downloaded my PBI file from here.
Hope this helps.
Thank you @Ashish_Mathur! You're right that it's six (6) - I put the wrong end date (should've been 31-Dec-2019) so I'll make an edit.
Your solution works but I forsee there being an issue with the table generated - it's only seven (7) activities yet it produces a table of over 2,300 rows, so I don't think it would be feasible for a system of thousands of actvities.
Any other suggestions?