Dynamic x-axis min/max range

[Anonymous]

Hello,

I have a time-series graph and my goal is to have the x-axis min and max dates adjust automatically based on the slicer selected value's start and end dates.

1. Sample data/scenario:

Say I have 3 different tables: 1) 'Student GPA' which has information for 4 different Student_IDs and how the GPA changes over time for each one; 2) 'Number of Exams Taken' which shows the number of exams taken for the same Student_IDs; 3) 'Dates to Use' which shows x_min and x_max dates for each Student_ID and these are the dates that I want to use on the x-axis. If you notice, the Date and Student_ID columns are exactly the same and in the same order in all three tables. In 'Dates to Use' table, each Student_ID has a unique x_min and x_max combination, they are just listed multiple times because the same Student_ID appears multiple times in the table.

1) Student GPA table

Date	Student_ID	GPA
1/5/2015	0001	3.1
2/1/2015	0002	3.2
3/3/2015	0003	2.9
4/1/2015	0004	3.1
5/1/2015	0001	3.2
8/1/2015	0003	3.8
12/1/2015	0004	3.5
1/3/2016	0001	3.5
5/4/2016	0002	3.6
6/30/2016	0002	3.9
9/12/2016	0003	3.9
5/30/2016	0004	3.6
1/5/2017	0003	3.9
2/2/2017	0004	3.8

2) Number of Exams Taken table

Date	Student_ID	Num_Exams
1/5/2015	0001	2
2/1/2015	0002	1
3/3/2015	0003	3
4/1/2015	0004	1
5/1/2015	0001	3
8/1/2015	0003	4
12/1/2015	0004	6
1/3/2016	0001	5
5/4/2016	0002	4
6/30/2016	0002	5
9/12/2016	0003	6
5/30/2016	0004	9
1/5/2017	0003	8
2/2/2017	0004	10

3) Dates to Use table

Date	Student_ID	x_min	x_max
1/5/2015	0001	2/1/2015
2/1/2015	0002	3/2/2015	7/3/2016
3/3/2015	0003	4/3/2015
4/1/2015	0004	6/3/2015	2/3/2017
5/1/2015	0001	2/1/2015
8/1/2015	0003	4/3/2015
12/1/2015	0004	6/3/2015	2/3/2017
1/3/2016	0001	2/1/2015
5/4/2016	0002	3/2/2015	7/3/2016
6/30/2016	0002	3/2/2015	7/3/2016
9/12/2016	0003	4/3/2015
5/30/2016	0004	6/3/2015	2/3/2017
1/5/2017	0003	4/3/2015
2/2/2017	0004	6/3/2015	2/3/2017

2. How the tables are linked:

There are two separate dimension tables: Student_ID and Date, both were created using the Student_ID and Date columns from 'Student GPA' and 'Number of Exams Taken' tables, respectively. So the 'Student GPA' and 'Number of Exams Taken' tables are connected through both of these dimension tables.

3. What I want my report to show:

The report should display 'GPA' and 'Num_Exams' over time for each unique Student_ID over time. I also have a slicer where one can selected Student_ID and the graph updates accordingly. The x-axis is currently using the date dimension table date.

What I want my report to do is the following:

Once the user selects a specific Student_ID from the slicer, the x-axis should automatically adjust the x-axis range to use the x_min and x_max columns from the 'Dates to Use' table. So for example, currently the x-axis is using min value of 1/5/2015 and max value of 2/2/2017. If I select Student_ID 0002, then the x-axis range should automatically update to use 3/2/2015 as x_min and 7/3/2016 as x_max. This should be done by looking up by matching on the Student_ID column and picking the earliest of the x_min/x_max dates given that they are repeated multiple times. If there is no x_max date (for example, for 0001), then it should use the original date that was in the date dimension table.

Thanks in advance.

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

You may create a measure to filter the dates for x-axis. For example:

IF(SELECTEDVALUE(axis_dates)>=MIN(slicer_dates)&&SELECTEDVALUE(axis_dates)<=MAX(slicer_dates),1,0)

Then put this measure to visual level filter that filter measure = 1.

Best Regards,

Jay

[Anonymous]

@v-jayw-msft Thank you.

[lbendlin]

that's controlled by which values you add to the visual. If you include values from both fact tables then the entire covering range will be used.

[Anonymous]

Thanks for clarifying. 

[Anonymous]

@lbendlin Thank you for your suggestion. 

Something that I forgot to mention in my question was that the date range for one of the tables could be smaller/larger than the second table. Basically, what I'm looking to do is that I'm plotting two variables/columns (in this case, it's GPA and Num_Exams), each coming from a different table, and I only have one date column that is coming from the date dimension table. How can I limit the x-axis to the range that is available only in one of those series (i.e. one of the columns in one of those two tables)?

Thanks.

[lbendlin]

You might be overcomplicating things.  Use the date from the calendar table as the x axis of the visual. Feed the slicer from the Student dimension table. Now when you  select a student it will automatically restrict the date range on the chart to the available data.