topic Re: Highest score based on 2 variables in DAX Commands and Tips

Highest score based on 2 variables

hans263 — Fri, 09 Sep 2022 08:19:28 GMT

Hi experts, I have an issue regarding a table with staffmembers, training they followed and the scores given for each training.

Table looks like this:

The result should be that for each department the number of maximimum(highest) scores are returned (highest score for each staffmember), and also a sum of maximum scores per department. I have used RANKX but there is an issue regarding strings and integerrs. Very anxious to see what solutions are available.

Re: Highest score based on 2 variables

tamerj1 — Fri, 09 Sep 2022 08:51:31 GMT

Hi hans263
Please refer to attached sample file with the solution

Count of Highest Scores = VAR CurrentScore = SELECTEDVALUE ( 'Table'[TrainingTypescore] ) RETURN COUNTROWS ( FILTER ( VALUES ( 'Table'[Staffmember] ), CALCULATE ( MAX ( 'Table'[TrainingTypescore] ), ALL ('Table'[NameTrainingFollowed], 'Table'[TrainingTypescore] ) ) = CurrentScore ) ) + 0

Re: Highest score based on 2 variables

hans263 — Fri, 09 Sep 2022 10:37:06 GMT

Yep. This works great! Many thx.

Re: Highest score based on 2 variables

hans263 — Fri, 09 Sep 2022 11:47:05 GMT

Additionale question: Current Score holds the maximum of found results. If I want to use that value, how can I get this back into a Column? Or otherwise, the number of unique staff members for echt department. OPS=1, Sales=3

Re: Highest score based on 2 variables

tamerj1 — Fri, 09 Sep 2022 12:16:09 GMT

hans263
Modified file attached

Count of Highest Scores = SUMX ( VALUES ( 'Table'[NameTrainingFollowed] ), CALCULATE ( VAR CurrentScore = SELECTEDVALUE ( 'Table'[TrainingTypescore] ) RETURN COUNTROWS ( FILTER ( VALUES ( 'Table'[Staffmember] ), CALCULATE ( MAX ( 'Table'[TrainingTypescore] ), ALL ('Table'[NameTrainingFollowed], 'Table'[TrainingTypescore] ) ) = CurrentScore ) ) + 0 ) )

Re: Highest score based on 2 variables

hans263 — Mon, 12 Sep 2022 10:13:53 GMT

Awesome. Last question. I finaly want to show the response. So in fact Count of Highest Score devided by the total number of staff members for a give department in %.

Re: Highest score based on 2 variables

tamerj1 — Mon, 12 Sep 2022 12:20:52 GMT

hans263
See attached modified file

% of Highest Score = VAR Numerator = [Count of Highest Scores] VAR Denominator = COUNTROWS ( CALCULATETABLE ( VALUES ( 'Table'[Staffmember] ), ALLEXCEPT ( 'Table', 'Table'[Department] ) ) ) VAR Result = DIVIDE ( Numerator, Denominator ) RETURN Result