Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone!
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 |
A | 01-Jan-2019 | 31-Dec-2019 |
B | 11-Oct-2018 | 24-Apr-2019 |
C | 05-Mar-2018 | 26-Feb-2020 |
D | 11-Aug-2019 | 05-Nov-2020 |
E | 17-May-2019 | 16-Sep-2019 |
F | 03-Mar-2018 | 05-Jun-2018 |
G | 02-Jan-2020 | 01-Jan-2021 |
If I were to use my slicer like so:
Start | 1-Jan-19 |
End | 31-Dec-20 |
then I should get the following Activities:
Activity |
A |
B |
C |
D |
E |
This is because each of these Activities share occurrence dates with the slicer dates.
Edit:
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.
Using [ShowMeasure], defined previously:
ActivitiesCount = SUMX ( ADDCOLUMNS ( Table1; "_Res"; [ShowMeasure] ); [_Res] )
Hi,
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?
You are welcome. No other suggestions. Before you make any such assumption, please first try the solutio on your live dataset.
Using [ShowMeasure], defined previously:
ActivitiesCount = SUMX ( ADDCOLUMNS ( Table1; "_Res"; [ShowMeasure] ); [_Res] )
Hi,
I implemented this solution as I was facing same issue and this worked.
Thanks for above solution.
However, I am facing another problem with this solution.
When we export underlying data from the visual, it gives all rows from the data table (not the filtered rows as per logic applied)
I tried below as solution to solve the export problem.
Apply the "ActivitiesCount = 1" at visual level filter
But the visuals show wrong results if I apply this filter
Could you please suggest how I can et right rows in visual export as well.
Regards,
Deeksha
Hi @Anonymous
I don't quite follow. Could you perhaps share a .pbix in which you show what you are trying to do and what does not work?
Cheers
Hi @AlB ,
I have prepared one sample for this issue. (PFA the same)
Please take a look and suggest.
Regards,
Deeksha
@AlB , thanks! The semicolons don't work for me but if I switch them for commas it appears to work fine.
Hi @jmeccles
Try this:
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |