Combining two x-axes from same table to plot two measures on same graph

[MJEnnis]

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?

[lbendlin]

modify your measures so that they use your new x axis reference column (via SELECTEDVALUE or an appropriate aggregation like MAX )

[MJEnnis]

@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_Met

When 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!!

[lbendlin]

Did you put the date column from the new table on the X axis?  Maybe post the sample PBI here.

[MJEnnis]

@lbendlin There is no date column. Just a whole number column which counts the number of days. That is of course based on the number of days between two dates. In theory, I could peel back the onion to access those dates in the measure and the new calcuated table, but not worth the hassle at this stage. I need the count column as I will be runing the survivor analysis package in R (if I can get it to work right in Power Bi). Cannot share the data, for privacy reasons, but what you see in the table above is what you get. 

[lbendlin]

Please provide sanitized sample data that fully covers your issue.

[lbendlin]

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.

[MJEnnis]

@lbendlin, should the new table be all theoreticla discrete values between the min and max of the x-axes, or should it only include a distinct select of all the actual values in the two "days" columns?

[lbendlin]

That's up to you to decide. You can set the axis type to continuous or categorical depending on your needs.

[MJEnnis]

Okay, so without any relationship, how do I get the two measures to recognize the new table as the input variable? I have created a new table using DISTINCT(UNION(SELECTCOLUMNS(...

But the graph shows two contant lines which are approximately the minimum of the respective survivor curves...