Using overall maximum for a gauge visual

[Anonymous]

Hi,

I have a dataset of student performance data with test results, student id and various demographic details. I have created a 'profile' page for each student containing demographic data as well as the test result data - this is obtained using the drill-through option. What I'm interested in showing is a gauge with the student's test result compared to the maximum mark achieved by any student in the dataset (this would be the max value of the gauge). To do this I created a measure as follows:

Maximum Student Score = CALCULATE(MAX('Student Data'[FINAL_MARKS]), ALL('Student Data'[ID]))

The problem is that every time I open the student profile page using the drill-through method, the maximum value is always the same as the student's test score (so the gauge is always full). Is my measure defined incorrectly, or is there something else that I am not doing right?

Thank you,

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

You may need to use this measure 

Maximum Student Score = CALCULATE(MAX('Student Data'[FINAL_MARKS]), ALL('Student Data'))

or

Maximum Student Score = CALCULATE(MAX('Student Data'[FINAL_MARKS]), ALLEXCEPT('Student Data', 'Student Data'[ID]))

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

Regards,

Lin

[Anonymous]

@v-lili6-msft  - that worked! Thank you very much for that.

[Anonymous]

Whether it's correct or not depends on what filters are in place at the time this measure is being applied and what your model looks like.

If you filter on the name of the student, then this ALL filter does not do anything as long as the names are unique.

If you want to remove all filters from the 'Student Data' table, you should use ALL( 'Student Data' ).

Best
D

[amitchandak]

Try like

Maximum Student Score = CALCULATE(MAX('Student Data'[FINAL_MARKS]), ALL('Student Data'))

or
Maximum Student Score = MAXX(ALL('Student Data'),'Student Data'[FINAL_MARKS])