Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

DAX measure to rank zeroes based on another column

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

Student NameAttendence (%)Total MarkExpected Ranking
Ian70501
Becky75482
Cathy80383
John50384
Denny60205
Kin65106
Lena6377
Mark7238
Ean9009
Nora80010
Fahl700

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.

 

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

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 ()
                )
        )
    )

View solution in original post

4 REPLIES 4
wdx223_Daniel
Super User
Super User

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 ()
                )
        )
    )

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

it's a measure

=SUM(Table[Mark])

Thank you. That seems to work!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors