Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CheenuSing
Community Champion
Community Champion

Getting detailed records as table based on status as of date is not showing properly

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.

ProjectStart DateEnd DateStatus
10152264/1/2011Monday, 1 August 20111
10206783/1/2011Monday, 14 March 20111
102067815/3/2011Wednesday, 14 March 20122
102068419/1/2011Wednesday, 14 September 20111
102108731/1/2011Friday, 27 May 20111
10217494/2/2011Friday, 27 May 20111
10217504/3/2011Wednesday, 16 November 20111
10217519/3/2011Monday, 20 June 20111
10217558/2/2011Wednesday, 25 May 20111
102175926/3/2011Thursday, 8 September 20111
10230765/1/2011Monday, 5 December 20111
10232011/2/2011Wednesday, 2 March 20111
10232013/3/2011Saturday, 17 March 20122
102324912/1/2011Thursday, 28 April 20111
102328314/1/2011Monday, 7 March 20111
10232838/3/2011Saturday, 3 March 20122
102328527/1/2011Tuesday, 29 March 20111
102328530/3/2011Wednesday, 28 March 20122
10232865/3/2011Thursday, 5 May 20111
102328711/2/2011Sunday, 4 September 20111
102328828/1/2011Friday, 21 October 20111
102329014/3/2011Tuesday, 17 May 20111
102329118/2/2011Friday, 15 April 20111
102329323/2/2011Friday, 29 April 20111
10232941/3/2011Tuesday, 23 August 20111
10232951/3/2011Friday, 22 April 20111
102329618/3/2011Friday, 3 June 20111
10232975/3/2011Monday, 20 June 20111
102329811/3/2011Thursday, 9 June 20111
102329928/3/2011Friday, 8 July 20111
102330018/3/2011Thursday, 16 June 20111
102330123/3/2011Friday, 21 October 20111
102330228/3/2011Wednesday, 3 August 20111
102336618/2/2011Thursday, 23 August 20121
102337014/2/2011Saturday, 28 May 20111
10234164/2/2011Monday, 28 March 20111
102341629/3/2011Tuesday, 27 March 20122
102345817/1/2011Monday, 21 February 20111
102345822/2/2011Tuesday, 21 February 20122
102346919/1/2011Thursday, 31 March 20111
102389225/1/2011Friday, 29 April 20111
10240497/2/2011Thursday, 12 May 20111
102444521/2/2011Saturday, 21 May 20111
102444617/2/2011Friday, 29 April 20111
102452225/2/2011Thursday, 17 March 20111
102452218/3/2011Friday, 16 March 20122
102502924/2/2011Thursday, 30 June 20111
102520725/3/2011Tuesday, 15 November 20111
102520825/3/2011Tuesday, 15 November 20111
102532515/3/2011Thursday, 14 April 20111
102532616/3/2011Monday, 25 April 20111
10253449/3/2011Thursday, 8 September 20111
102550231/3/2011Friday, 31 August 20121
103084725/2/2011Wednesday, 25 May 20111
103161723/1/2011Wednesday, 2 May 20121

 

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

ProjectsByStatus =
var current_date = MAX('Calendar'[Date])
return
CALCULATE(DISTINCTCOUNT('Data'[Project]), current_date>= 'Data'[Start Date] && current_date <= 'Data'[End Date],ALLSELECTED('Calendar') )

 

Using this I created a stacked column chart - Year & Month as X-Axis, ProjectByStatus as Y-Axis and Status as legend

CheenuSing_0-1658743456467.png

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

 

 

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
1 ACCEPTED SOLUTION
AntonioM
Solution Sage
Solution Sage

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').

 

AntonioM_0-1658746657999.png

 

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.

 

AntonioM_1-1658746658003.png

 

 

I would unlink your calendar table to data, and then use a measure to filter the table, such as

 

Table Filter =
var current_date = MAX('Calendar'[Date])
return
IF(
HASONEVALUE('Calendar'[Date Month]),
IF(
SELECTEDVALUE(Data[Start Date]) <= current_date && SELECTEDVALUE(Data[End Date]) >= current_date, 1, 0),
1
)

 

which will give you the records included in the count, or all of the rows if nothing is selected.

AntonioM_3-1658746941512.png

 

View solution in original post

1 REPLY 1
AntonioM
Solution Sage
Solution Sage

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').

 

AntonioM_0-1658746657999.png

 

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.

 

AntonioM_1-1658746658003.png

 

 

I would unlink your calendar table to data, and then use a measure to filter the table, such as

 

Table Filter =
var current_date = MAX('Calendar'[Date])
return
IF(
HASONEVALUE('Calendar'[Date Month]),
IF(
SELECTEDVALUE(Data[Start Date]) <= current_date && SELECTEDVALUE(Data[End Date]) >= current_date, 1, 0),
1
)

 

which will give you the records included in the count, or all of the rows if nothing is selected.

AntonioM_3-1658746941512.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.