I have a requirement where i need to draw a Line Chart, for e.g I have two tables - one is Session Table to record each session start and end date.
2nd is Student table to record registration date of student and session id will be foreign key in Student table.
SessionID, Startdate, EndDate are the columns in my Session Table.
StudentID,RegistrationDate,SessionID are columns in my Student Table.
1. Where start and end dates are different for each Session.
2. On Selection of Session, Start and End date will be drawn on X-axis with 15 days interval.
3. Y-axis will show count of Student where Student's registration date is in between start and end date of selected Session.
I need to dran a line chart where x-axis will date range starts from StartDate of selected session and ends on Enddate of selected Sesssion and Y-axis will give me count of student where student's registration date is falling between start and end date of sselected session. In Student table, many student can have same registration date.
I am just able to draw line chart with student's registration date on X-axis and count on Y-axis from Student Table. I need to show Start and end date as well on X-axis from Session table.
Could you please advise me a better approach.
What is wrong (what don't you like) with your current approach?
I think there are issues with model design and measures:
You can improve the model design by:
Separate facts (data) from dimensions (lookups) into different tables.
Clarify you data: Are start and end dates against the Session or against the Student or against the Registration (I have a guess, but I should not ever use a guess)?
Also, what are the the business questions you are trying to answer with your chart (this will determine the type of measure to write)?
Thanks for response.
I have updated my question, would give a clear scenerio.
I have to show a data on line chart where based on date column and this date value shoud be fall between start and end date which is coming from another table. And this start and end date should be visible on X-axis.
I'm not sure if I understand your question clearly. As you said, each student is associated with a Session, you can create a column to tag if current student can be counted as 1.
IfCount= IF(Student[RegistrationDate]>Session[StartDate] && Student[RegistrationDate]>Session[EndDate],1,0)
Then you can sum above column on Session Level to get total count. I think you can use M query to add a custom column, each entry is a Table object which is a list of dates between start date and end date, then expand them for populating on X Axis.