topic Re: DAX measure to rank zeroes based on another column in DAX Commands and Tips

DAX measure to rank zeroes based on another column

Anonymous — Tue, 28 Mar 2023 10:08:23 GMT

Hi,
Could you please help me with a measure that can rank the zero values based on another column.
Sample dataset:

Student Name	Attendence (%)	Total Mark	Expected Ranking
Ian	70	50	1
Becky	75	48	2
Cathy	80	38	3
John	50	38	4
Denny	60	20	5
Kin	65	10	6
Lena	63	7	7
Mark	72	3	8
Ean	90	0	9
Nora	80	0	10
Fahl	70	0	11

The column "Expected Ranking" is the outcome of a measure that ranks the students based on the "Total Mark" column. But when it comes to the zero marks it should rank based on the "Attendance(%)" column.

To summarize, whenever there is a tie in marks, it should consider the Attendance column and rank accordingly.
(Row 3 and 4 are tie and it ranked based on Attendance column)

Any help on this appreciated.

Thank you.

Re: DAX measure to rank zeroes based on another column

wdx223_Daniel — Tue, 28 Mar 2023 07:53:39 GMT

Ranking =
VAR _mark = [Total Mark]
RETURN
    COUNTROWS (
        FILTER (
            ALLSELECTED ( Table[Student Name] ),
            [Total Mark] >= _mark
                && IF (
                    [Total Mark] = _mark,
                    'Table'[Student Name] <= MAX ( 'Table'[Student Name] ),
                    TRUE ()
                )
        )
    )

Re: DAX measure to rank zeroes based on another column

Anonymous — Tue, 28 Mar 2023 09:11:07 GMT

Thanks for the reply.
Is [Total Mark] a measure that is the sum of the Total Marks column?

Re: DAX measure to rank zeroes based on another column

wdx223_Daniel — Wed, 29 Mar 2023 01:34:06 GMT

it's a measure

=SUM(Table[Mark])

Re: DAX measure to rank zeroes based on another column

Anonymous — Mon, 03 Apr 2023 11:54:36 GMT

Thank you. That seems to work!