Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a requirement to only show the max student ID out of any given Class, as well as calculate related measures for that student only.
I have tried set analysis on the measures to control the records shown on the table but it is only evaluating correctly when I am filtering on a particular Class.
SUM({<[Student ID] = {'$(=aggr(max([Student ID]),[Class])'}>}[Score]
Also tried:
SUM({<[Student ID] = {'$(=rangemax(aggr(max([Student ID]),[Class]))'}>}[Score]
in both cases when i filter on a particular Class, i am able to get expected results but when i unfilter, the expression doesnt works. Any suggestions to evaluate across the whole dataset?
Dataset:
Class | Student ID | Score |
Class A | 2020001 | |
Class A | 2020002 | |
Class A | 2020003 | |
Class A | 2020004 | |
Class A | 2020005 | |
Class B | 2020006 | |
Class B | 2020007 | |
Class B | 2020008 | |
Class B | 2020009 |
|
Expected table output:
Class | Max Student ID | Score |
Class A | 2020005 | |
Class B | 2020009 |
@kerkying set analysis won't work here as set analysis works once per chart and not row by row so try below
=if([Student ID]= max(total <Class>[Student ID]),sum(Score),0)
Thanks for the reply, is there anyway i can hide the record if the expression returns 0?
Hi all, I've found a way:
SUM({<[Student ID] = {$(=concat(Aggr(distinct max([Student ID]),[Class]),','))}>}[Score]
Not sure why removing the single quotation around the evaluation will resolve the issue though.