Forum Discussion

MJEnnis's avatar
MJEnnis
Resolver III
3 years ago

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

  • 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's avatar
      MJEnnis
      Resolver III

      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's avatar
        lbendlin
        Super User

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

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

    • MJEnnis's avatar
      MJEnnis
      Resolver 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_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's avatar
        lbendlin
        Super User

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