DAX Rank()

Question

Hi All,&nbsp;I have created a table report with columns coming from Excel.&nbsp;Player&nbsp;&nbsp; Matches Avg&nbsp; StrikeRate&nbsp; RunsRohit&nbsp;&nbsp;&nbsp;&nbsp; 102&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 34&nbsp;&nbsp;&nbsp;&nbsp; 135&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4500Paul&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 112&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 32&nbsp;&nbsp;&nbsp;&nbsp; 140&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4567Rica&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;98&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 33&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 137&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4345&nbsp;I created a DAX columnCriteria = If( and(AND( and (Batsmen[SR]&gt;130, batsmen[avg]&gt;30),batsmen[mat]&gt;100),batsmen[runs]&gt;3000),1,0)&nbsp;Then the table in Power bi looks like below&nbsp;Player&nbsp;&nbsp; Matches Avg&nbsp; StrikeRate&nbsp; RunsRohit&nbsp;&nbsp;&nbsp;&nbsp; 102&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 34&nbsp;&nbsp;&nbsp;&nbsp; 135&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4500Paul&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 112&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 32&nbsp;&nbsp;&nbsp;&nbsp; 140&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4567&nbsp;Now, I want to apply "Rank" to the above filtered table. Please suggest how to go about it. Thanks!!&nbsp;Rank Player&nbsp;&nbsp; Matches Avg&nbsp; StrikeRate&nbsp; Runs&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp; Rohit&nbsp;&nbsp;&nbsp;&nbsp; 102&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 34&nbsp;&nbsp;&nbsp;&nbsp; 135&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4500&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp; Paul&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 112&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 32&nbsp;&nbsp;&nbsp;&nbsp; 140&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4567&nbsp;CheersJim&nbsp;

phil_seamark · Answer

HI&nbsp;Jim123456789jim&nbsp;This calculated column will give you a basic ranking.&nbsp; Just change the 'Table' to your own tablename.&nbsp;New Column = RANKX('Table','Table'[Runs],,ASC)

jim123456789jim · Answer

HI,&nbsp;Thanks for your reply, but this solution is not helping. Reason, I want to give rank after filtering. For ex,&nbsp;Assume the orginal table in this order,&nbsp;Player&nbsp;&nbsp; Matches Avg&nbsp; StrikeRate&nbsp; RunsRohit&nbsp;&nbsp;&nbsp;&nbsp; 102&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 34&nbsp;&nbsp;&nbsp;&nbsp; 135&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4500Rica&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;98&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 38&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 137&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4645&nbsp; --- Rica will be rejected as Matches is less than 100Paul&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 112&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 32&nbsp;&nbsp;&nbsp;&nbsp; 140&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4567&nbsp;I want the below as the output in the table visualization after filtering with the DAX conditionCriteria = If( and(AND( and (Batsmen[SR]&gt;130, batsmen[avg]&gt;30),batsmen[mat]&gt;100),batsmen[runs]&gt;3000),1,0)&nbsp;Rank Player&nbsp;&nbsp; Matches Avg&nbsp; StrikeRate&nbsp; Runs&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp; Paul&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 112&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 32&nbsp;&nbsp;&nbsp;&nbsp; 140&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4567&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp; Rohit&nbsp;&nbsp;&nbsp;&nbsp; 102&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 34&nbsp;&nbsp;&nbsp;&nbsp; 135&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4500&nbsp;Reason , I want to apply Rank only to those rows which satisfy the condition Batsmen[SR]&gt;130, batsmen[avg]&gt;30),batsmen[mat]&gt;100,batsmen[runs]&gt;3000 and order by Runs Descending.&nbsp;Now, if I apply your formula. The output will be&nbsp;Rank Player&nbsp;&nbsp; Matches Avg&nbsp; StrikeRate&nbsp; Runs2&nbsp;&nbsp;&nbsp; Rohit&nbsp;&nbsp;&nbsp;&nbsp; 102&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 34&nbsp;&nbsp;&nbsp;&nbsp; 135&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 45003&nbsp;&nbsp;&nbsp;&nbsp; Paul&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 112&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 32&nbsp;&nbsp;&nbsp;&nbsp; 140&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4567&nbsp;Even if I change your DAX to descending, the output is,&nbsp;Rank Player&nbsp;&nbsp; Matches Avg&nbsp; StrikeRate&nbsp; Runs2&nbsp;&nbsp;&nbsp;&nbsp; Paul&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 112&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 32&nbsp;&nbsp;&nbsp;&nbsp; 140&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 45673&nbsp;&nbsp;&nbsp; Rohit&nbsp;&nbsp;&nbsp;&nbsp; 102&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 34&nbsp;&nbsp;&nbsp;&nbsp; 135&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4500&nbsp;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.&nbsp;Thanks!!&nbsp;&nbsp;&nbsp;Reason, as per the original data (without filtering) Rica is having maximum Runs followed by Paul and Rohit&nbsp;

phil_seamark · Answer

Hi&nbsp;Jim123456789jim&nbsp;If you would like it to respect filter settings it needs to be a calculated measure.&nbsp;Please try adding the following and adding to a Grid or Matrix&nbsp;Measure = 1+CALCULATE(
		COUNTROWS('Table'),
		FILTER(
			ALLSELECTED('Table'),
			'Table'[Runs]&lt;MAX('Table'[Runs])
			)
			)

phil_seamark · Answer

Actuall, I see what you are after.&nbsp; I will post the calculation in an hour.&nbsp; I just have to head out the door. :)

zubair_muhammad · Answer

Jim123456789jim&nbsp;Try this as a calculated column&nbsp;RANK =
IF (
    Batsmen[Criteria] = 1,
    RANKX ( FILTER ( Batsmen, Batsmen[Criteria] = 1 ), [Runs],, DESC, DENSE )
)

Forum Discussion

DAX Rank()

7 Replies

Related content

DAX Measure for Ranking the valid rows and calculating the average rating

Rank DAX formula

Duplicate ranking

DAX Ranking Issue

RANK and RANKX DAX function