Line Graph data points filtering off of previous data point

[MClipsham92]

Hello All,

I have a line graph which shows the sum of the overall score of students on assessments at 3 different assessment points. However the number of students completing the assessment can change between assessment points. How do I filter the line graph so that it only includes overall scores from student which were in the last assessment point? 

Currently I have this:

As we can see next to the visual level filters in the lowwer right I have 17 students at Link Teacher Score 1, 16 at Link Teacher Score 2 and 18 at Link Teacher score 3. How do I make it so the same 16 students are used for all 3?

Data looks like this:

Identifier Score 1 Score 2 Score 3 Score 4 Overall Score

Student             3          4           1            5                 13

Thanks,

Mark.

[Anonymous]

Hi @MClipsham92 ,

You can do unpivot columns function on your table to convert multiple scores and value columns to attribute and value.
Drag score attribute to legend fields, value to value field of line chart, then you can see all scores types.

Unpivot Data Using Excel Power Query

Regards,

Xiaoxin Sheng

[MClipsham92 · Accepted solution]

Thanks for your reply.

I've already unpivotted all of the data to make it work as a Line Chart, this was not the issue.

The issue was limiting the line chart to only use data from students that have completed all three assessment points. I've managed to solve it by writing the following DAX for a new column and then using this column as a visual level filter in the Line Chart, seems a bit inelegant but looks like it works;

Appears in All surveys = 
 VAR a= IF('Link Teacher'[Attribute]="Link Teacher Score 3",MAXX(FILTER('Link Teacher','Link Teacher'[Identifier]=EARLIER('Link Teacher'[Identifier])&&'Link Teacher'[Attribute]="Link Teacher Score 1"),'Link Teacher'[Score]),MAXX(FILTER('Link Teacher','Link Teacher'[Identifier]=EARLIER('Link Teacher'[Identifier])&&'Link Teacher'[Attribute]="Link Teacher Score 1"),'Link Teacher'[Score]))
  VAR b= IF('Link Teacher'[Attribute]="Link Teacher Score 2",MAXX(FILTER('Link Teacher','Link Teacher'[Identifier]=EARLIER('Link Teacher'[Identifier])&&'Link Teacher'[Attribute]="Link Teacher Score 2"),'Link Teacher'[Score]),MAXX(FILTER('Link Teacher','Link Teacher'[Identifier]=EARLIER('Link Teacher'[Identifier])&&'Link Teacher'[Attribute]="Link Teacher Score 2"),'Link Teacher'[Score]))
  VAR c= IF('Link Teacher'[Attribute]="Link Teacher Score 1",MAXX(FILTER('Link Teacher','Link Teacher'[Identifier]=EARLIER('Link Teacher'[Identifier])&&'Link Teacher'[Attribute]="Link Teacher Score 3"),'Link Teacher'[Score]),MAXX(FILTER('Link Teacher','Link Teacher'[Identifier]=EARLIER('Link Teacher'[Identifier])&&'Link Teacher'[Attribute]="Link Teacher Score 3"),'Link Teacher'[Score]))
  RETURN IF(ISBLANK(a),BLANK(),IF(ISBLANK(b),BLANK(),IF(ISBLANK(c),BLANK(),"In all 3")))

Thanks,

Mark.