Forum Discussion
Combining two x-axes from same table to plot two measures on same graph
I have seen several examples of how to plot two columns from two different tables by a common X axis. But I have somewhat more complicated situation.
The table looks something like this:
|
Student ID |
Time to Exit Status |
Exit Status |
Time to L3 Status |
L3 Status |
|
1 |
0 |
2 At Matriculation |
0 |
2 At Matriculation |
|
2 |
3 |
3 On time |
3 |
3 On time |
|
3 |
55 |
3 On time |
0 |
3 On time |
|
4 |
1500 |
4 Late |
500 |
4 Late |
|
5 |
|
1 N/A |
|
1 N/A |
|
6 |
1300 |
4 Late |
300 |
3 On time |
|
7 |
450 |
5 Not yet |
440 |
4 Late |
The two time columns count the days to the current/final status displayed in the adjacent columns. I am building a visual that displays the survival curve for both statuses. (That is, the percentage of the population who have not achieved the target status by the number of days which half passed since the initial start date. You can see the basic idea here.) To do this, I have created two measures that calculate the percentages so that they can be graphed by number of days passed. This strategy works perfectly when plotting the measures separately. All the various filters in the model work on the resulting visuals. But, ideally, I would plot both measures on the same graph using a common X axis.
I can think of an easy way to do this, but not an easy way that is "proper".
One solution would be to create two new calculated tables, one with Exit Status data and one with L3 Status data, recreate the measures based on those tables, and then distinct union the two time (day count) columns into a third table. I could then relate the original table to both new tables in order to integrate them into the model.
So I would have two tables like this:
|
Student ID |
Time to Exit Status |
Exit Status |
- |
Student ID |
Time to L3 Status |
L3 Status |
|
1 |
0 |
2 At Matriculation |
- |
1 |
0 |
2 At Matriculation |
|
2 |
3 |
3 On time |
- |
2 |
3 |
3 On time |
|
3 |
55 |
3 On time |
- |
3 |
0 |
3 On time |
|
4 |
1500 |
4 Late |
- |
4 |
500 |
4 Late |
|
5 |
|
1 N/A |
- |
5 |
|
1 N/A |
|
6 |
1300 |
4 Late |
- |
6 |
300 |
3 On time |
|
7 |
450 |
5 Not yet |
- |
7 |
440 |
4 Late |
Another option would be to unpivot the selected data above via union and select columns, to create a new stacked table. That would be a more proper solution from a data science perspective, but I would have to rewrite the measures signifcantly and test how the relationships would work. That would be something like this:
|
Student ID |
Time Status |
Status |
Status Type |
|
1 |
0 |
2 At Matriculation |
Exit |
|
2 |
3 |
3 On time |
Exit |
|
3 |
55 |
3 On time |
Exit |
|
4 |
1500 |
4 Late |
Exit |
|
5 |
|
1 N/A |
Exit |
|
6 |
1300 |
4 Late |
Exit |
|
7 |
450 |
5 Not yet |
Exit |
|
1 |
0 |
2 At Matriculation |
L3 |
|
2 |
3 |
3 On time |
L3 |
|
3 |
0 |
3 On time |
L3 |
|
4 |
500 |
4 Late |
L3 |
|
5 |
|
1 N/A |
L3 |
|
6 |
300 |
3 On time |
L3 |
|
7 |
440 |
4 Late |
L9 |
My model is already quite large, and I want to avoid additional tables if possible.
Essentially, I want to turn this:
Into this:
Note that the final image is simply one graph stacked on top of the other on the page. With the axes fixed, it looks quite nice, visually. Unfortunately, this is not ideal, either, because I would lose some of the interactions with the “bottom” graph both in the desktop app and when I share the reports online.
I know that many of you are way savvier than me. Any better ideas out there?
9 Replies
- lbendlinSuper User
Whatever represents your X axis - put it into a disconnected table. Then compute the two measures. add these three fields to the line chart visual.
- lbendlinSuper User
That's up to you to decide. You can set the axis type to continuous or categorical depending on your needs.
- lbendlinSuper User
modify your measures so that they use your new x axis reference column (via SELECTEDVALUE or an appropriate aggregation like MAX )
- MJEnnisResolver III
lbendlin I think I am missing something...
Here is the structure of the reference table:
Student ID
Time to Exit Status
Exit Status
Time to L3 Status
L3 Status
1
0
2 At Matriculation
0
2 At Matriculation
2
3
3 On time
3
3 On time
3
55
3 On time
0
3 On time
4
1500
4 Late
500
4 Late
5
1 N/A
1 N/A
6
1300
4 Late
300
3 On time
7
450
5 Not yet
440
4 Late
Here is one of the measures:
Not Met = Var TOT_STS = Calculate(COUNT('Student Language Levels'[Time to Exit Status]), 'Student Language Levels'[Exit Status] <> "1 N/A" && NOT(ISBLANK('Student Language Levels'[Time to Exit Status])) ) Var TOT_MET = Calculate(COUNT('Student Language Levels'[Time to Exit Status]), NOT('Student Language Levels'[Exit Status] IN {"1 N/A", "5 Not Yet"}) && NOT(ISBLANK('Student Language Levels'[Time to Exit Status])), FILTER(ALLSELECTED('Student Language Levels'), 'Student Language Levels'[Time to Exit Status] <= MAX('Student Language Levels'[Time to Exit Status])) ) Var Percent_NOT_Met = Divide(TOT_STS-TOT_MET,TOT_STS) RETURN Percent_NOT_MetWhen I plot the measures against their respective "time" columns in the reference table, the respective plots work perfectly. But when I plot them against the new "days" table, I get a constant line which corresponds to the minimum value calculated by each measure.
Do I simply add a filter to the two measures to set the selected(time to status) to Days[day]? Something like && SELECTEDVALUE('Student Language Levels'[Time to Exit Status]) = 'Days'[Days] ?Thanks a lot!!
- lbendlinSuper User
Did you put the date column from the new table on the X axis? Maybe post the sample PBI here.