Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
This calculated column will give you a basic ranking. Just change the 'Table' to your own tablename.
New Column = RANKX('Table','Table'[Runs],,ASC)
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
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]) ) )
Actuall, I see what you are after. I will post the calculation in an hour. I just have to head out the door. 🙂
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]) )
Try this as a calculated column
RANK = IF ( Batsmen[Criteria] = 1, RANKX ( FILTER ( Batsmen, Batsmen[Criteria] = 1 ), [Runs],, DESC, DENSE ) )
As a Measure
RANK Measure = IF ( SELECTEDVALUE ( Batsmen[Criteria] ) = 1, RANKX ( FILTER ( ALLSELECTED ( Batsmen ), Batsmen[Criteria] = 1 ), CALCULATE ( MAX ( Batsmen[Runs] ) ), , DESC, DENSE ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |