Forum Discussion

MClipsham92's avatar
MClipsham92
Frequent Visitor
7 years ago
Solved

Line Graph data points filtering off of previous data point

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.

Need Help
  • MClipsham92's avatar
    MClipsham92
    7 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      MClipsham92
      Frequent Visitor
      7 years ago

      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.