DAX Rank()

[Jim123456789jim]

Hi All,

I have created a table report with columns coming from Excel.

Player   Matches Avg  StrikeRate  Runs

Rohit     102         34     135            4500

Paul       112         32     140             4567

Rica        98        33      137            4345

I created a DAX column

Criteria = If( and(AND( and (Batsmen[SR]>130, batsmen[avg]>30),batsmen[mat]>100),batsmen[runs]>3000),1,0)

Then the table in Power bi looks like below

Player   Matches Avg  StrikeRate  Runs

Rohit     102         34     135            4500

Paul       112         32     140             4567

Now, I want to apply "Rank" to the above filtered table. Please suggest how to go about it. Thanks!!

Rank Player   Matches Avg  StrikeRate  Runs

  1     Rohit     102         34     135            4500

  2     Paul       112         32     140             4567

Cheers

Jim

[Phil_Seamark]

HI @Jim123456789jim

This calculated column will give you a basic ranking.  Just change the 'Table' to your own tablename.

New Column = RANKX('Table','Table'[Runs],,ASC)

[Jim123456789jim]

HI,

Thanks for your reply, but this solution is not helping. Reason, I want to give rank after filtering. For ex,

Assume the orginal table in this order,

Player   Matches Avg  StrikeRate  Runs

Rohit     102         34     135            4500

Rica        98          38      137            4645  --- Rica will be rejected as Matches is less than 100

Paul       112         32     140             4567

I want the below as the output in the table visualization after filtering with the DAX condition

Criteria = If( and(AND( and (Batsmen[SR]>130, batsmen[avg]>30),batsmen[mat]>100),batsmen[runs]>3000),1,0)

Rank Player   Matches Avg  StrikeRate  Runs

  1    Paul       112         32     140             4567

  2     Rohit     102         34     135            4500

Reason , I want to apply Rank only to those rows which satisfy the condition Batsmen[SR]>130, batsmen[avg]>30),batsmen[mat]>100,batsmen[runs]>3000 and order by Runs Descending.

Now, if I apply your formula. The output will be

Rank Player   Matches Avg  StrikeRate  Runs

2    Rohit     102         34     135            4500

3     Paul       112         32     140             4567

Even if I change your DAX to descending, the output is,

Rank Player   Matches Avg  StrikeRate  Runs

2     Paul       112         32     140             4567

3    Rohit     102         34     135            4500

Note : I'm looking to give RANK to the rows in the Report to only those rows which satisfy the filtering condition. Pls suggest how to go about it.

Thanks!!

Reason, as per the original data (without filtering) Rica is having maximum Runs followed by Paul and Rohit

[Phil_Seamark]

Hi @Jim123456789jim

If you would like it to respect filter settings it needs to be a calculated measure.

Please try adding the following and adding to a Grid or Matrix

Measure = 1+CALCULATE(
		COUNTROWS('Table'),
		FILTER(
			ALLSELECTED('Table'),
			'Table'[Runs]<MAX('Table'[Runs])
			)
			)

[Phil_Seamark]

Actuall, I see what you are after.  I will post the calculation in an hour.  I just have to head out the door. 🙂

[Phil_Seamark]

HI @Jim123456789jim

I belive this is what you are after.  This is a calculated column

My Rank = 
            CALCULATE(
                COUNTROWS('Table') ,
                FILTER(
                    'Table',
                    EARLIER('Table'[Runs]) > 3000 && 
                    EARLIER('Table'[Avg]) > 30 && 
                    EARLIER('Table'[Matches]) > 100 && 
                    EARLIER('Table'[StrikeRate]) > 130),
                'Table'[Runs] <= EARLIER('Table'[Runs])
                )

[Zubair_Muhammad]

@Jim123456789jim

Try this as a calculated column

RANK =
IF (
    Batsmen[Criteria] = 1,
    RANKX ( FILTER ( Batsmen, Batsmen[Criteria] = 1 ), [Runs],, DESC, DENSE )
)

[Zubair_Muhammad]

@Jim123456789jim

As a Measure

RANK Measure =
IF (
    SELECTEDVALUE ( Batsmen[Criteria] ) = 1,
    RANKX (
        FILTER ( ALLSELECTED ( Batsmen ), Batsmen[Criteria] = 1 ),
        CALCULATE ( MAX ( Batsmen[Runs] ) ),
        ,
        DESC,
        DENSE
    )
)