Forum Discussion
Line Graph data points filtering off of previous data point
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.
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
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.