Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I have a project table which has columns
Project, Start Date, End Date, Status
A Project will have multiple records based on Status from a given Start Date.
Project | Start Date | End Date | Status |
1015226 | 4/1/2011 | Monday, 1 August 2011 | 1 |
1020678 | 3/1/2011 | Monday, 14 March 2011 | 1 |
1020678 | 15/3/2011 | Wednesday, 14 March 2012 | 2 |
1020684 | 19/1/2011 | Wednesday, 14 September 2011 | 1 |
1021087 | 31/1/2011 | Friday, 27 May 2011 | 1 |
1021749 | 4/2/2011 | Friday, 27 May 2011 | 1 |
1021750 | 4/3/2011 | Wednesday, 16 November 2011 | 1 |
1021751 | 9/3/2011 | Monday, 20 June 2011 | 1 |
1021755 | 8/2/2011 | Wednesday, 25 May 2011 | 1 |
1021759 | 26/3/2011 | Thursday, 8 September 2011 | 1 |
1023076 | 5/1/2011 | Monday, 5 December 2011 | 1 |
1023201 | 1/2/2011 | Wednesday, 2 March 2011 | 1 |
1023201 | 3/3/2011 | Saturday, 17 March 2012 | 2 |
1023249 | 12/1/2011 | Thursday, 28 April 2011 | 1 |
1023283 | 14/1/2011 | Monday, 7 March 2011 | 1 |
1023283 | 8/3/2011 | Saturday, 3 March 2012 | 2 |
1023285 | 27/1/2011 | Tuesday, 29 March 2011 | 1 |
1023285 | 30/3/2011 | Wednesday, 28 March 2012 | 2 |
1023286 | 5/3/2011 | Thursday, 5 May 2011 | 1 |
1023287 | 11/2/2011 | Sunday, 4 September 2011 | 1 |
1023288 | 28/1/2011 | Friday, 21 October 2011 | 1 |
1023290 | 14/3/2011 | Tuesday, 17 May 2011 | 1 |
1023291 | 18/2/2011 | Friday, 15 April 2011 | 1 |
1023293 | 23/2/2011 | Friday, 29 April 2011 | 1 |
1023294 | 1/3/2011 | Tuesday, 23 August 2011 | 1 |
1023295 | 1/3/2011 | Friday, 22 April 2011 | 1 |
1023296 | 18/3/2011 | Friday, 3 June 2011 | 1 |
1023297 | 5/3/2011 | Monday, 20 June 2011 | 1 |
1023298 | 11/3/2011 | Thursday, 9 June 2011 | 1 |
1023299 | 28/3/2011 | Friday, 8 July 2011 | 1 |
1023300 | 18/3/2011 | Thursday, 16 June 2011 | 1 |
1023301 | 23/3/2011 | Friday, 21 October 2011 | 1 |
1023302 | 28/3/2011 | Wednesday, 3 August 2011 | 1 |
1023366 | 18/2/2011 | Thursday, 23 August 2012 | 1 |
1023370 | 14/2/2011 | Saturday, 28 May 2011 | 1 |
1023416 | 4/2/2011 | Monday, 28 March 2011 | 1 |
1023416 | 29/3/2011 | Tuesday, 27 March 2012 | 2 |
1023458 | 17/1/2011 | Monday, 21 February 2011 | 1 |
1023458 | 22/2/2011 | Tuesday, 21 February 2012 | 2 |
1023469 | 19/1/2011 | Thursday, 31 March 2011 | 1 |
1023892 | 25/1/2011 | Friday, 29 April 2011 | 1 |
1024049 | 7/2/2011 | Thursday, 12 May 2011 | 1 |
1024445 | 21/2/2011 | Saturday, 21 May 2011 | 1 |
1024446 | 17/2/2011 | Friday, 29 April 2011 | 1 |
1024522 | 25/2/2011 | Thursday, 17 March 2011 | 1 |
1024522 | 18/3/2011 | Friday, 16 March 2012 | 2 |
1025029 | 24/2/2011 | Thursday, 30 June 2011 | 1 |
1025207 | 25/3/2011 | Tuesday, 15 November 2011 | 1 |
1025208 | 25/3/2011 | Tuesday, 15 November 2011 | 1 |
1025325 | 15/3/2011 | Thursday, 14 April 2011 | 1 |
1025326 | 16/3/2011 | Monday, 25 April 2011 | 1 |
1025344 | 9/3/2011 | Thursday, 8 September 2011 | 1 |
1025502 | 31/3/2011 | Friday, 31 August 2012 | 1 |
1030847 | 25/2/2011 | Wednesday, 25 May 2011 | 1 |
1031617 | 23/1/2011 | Wednesday, 2 May 2012 | 1 |
I have a Calendar Table containing only the date and this is linked to Project Table - Start Date
I also have a measure to compute the number of projects in different status as upto that period
Using this I created a stacked column chart - Year & Month as X-Axis, ProjectByStatus as Y-Axis and Status as legend
Showing upto MAr 2011 only.
The figures are correct. As at 31 March - There are 41 Projects in Status - 1 and 7 in Status 2.
The challenge now is if I click on 7 in March I want to see all the seven Records in STatus -2 as a table visual ,
I am able to see only 6, all with start date in Mar, but there is one project (1023458) carry over from Feb that is retrieved by the measure.
Any help will be appreciated.
Cheers
CheenuSing
Solved! Go to Solution.
Hi,
So the project 1023458 has status 2 from Feb 2011 to Feb 2012, so it is picked up by the measure fine, as it has ALLSELECTED('Calendar').
When you click on the 7 in the chart, it applies the filter "2011 Mar" to the table, but because your calendar is linked on start date, this doesn't include 1023458, because its start date is in February.
I would unlink your calendar table to data, and then use a measure to filter the table, such as
which will give you the records included in the count, or all of the rows if nothing is selected.
Hi,
So the project 1023458 has status 2 from Feb 2011 to Feb 2012, so it is picked up by the measure fine, as it has ALLSELECTED('Calendar').
When you click on the 7 in the chart, it applies the filter "2011 Mar" to the table, but because your calendar is linked on start date, this doesn't include 1023458, because its start date is in February.
I would unlink your calendar table to data, and then use a measure to filter the table, such as
which will give you the records included in the count, or all of the rows if nothing is selected.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |