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'm attempting to use the RANK.EQ function to produce TopN and BottomN tables that can be filtered by rank.
Currently I have a TopN column that ranks Device CPU usage totals from highest to lowest with:
RankEq-TopN = RANK.EQ('All Device CPU Rank Table'[Total],'All Device CPU Rank Table'[Total],DESC)
And I'd like to produce the inverse for BottomN.
The issue I encounter is the 25 deactivated devices at the bottom with a CPU usage total of 0 that are all Rank 1 followed by the lowest active device that starts at Rank 26.
I've attempted to ignore the devices with a total of 0 with a pretty amateurish If statement:
RankEq-BottomN = IF('All Device CPU Rank Table'[Total]<>0,RANK.EQ('All Device CPU Rank Table'[Total],'All Device CPU Rank Table'[Total],ASC))
But my Rank value still starts at 26.
Does anyone have any suggestions as to how to ignore a specific value and still start the rank value at 1?
Edit: Any other suggestions to better acheive the same goal would also be appreciated
Solved! Go to Solution.
Try with RANKX
= IF ( 'All Device CPU Rank Table'[Total] <> 0, RANKX ( FILTER ( ALL ( 'All Device CPU Rank Table'[Total] ), 'All Device CPU Rank Table'[Total] <> 0 ), 'All Device CPU Rank Table'[Total], , ASC ) )
Try with RANKX
= IF ( 'All Device CPU Rank Table'[Total] <> 0, RANKX ( FILTER ( ALL ( 'All Device CPU Rank Table'[Total] ), 'All Device CPU Rank Table'[Total] <> 0 ), 'All Device CPU Rank Table'[Total], , ASC ) )
Thanks Zubair,
Worked exactly as required.
I attempted RANKX prior to RANK.EQ and for the life of me couldn't get anything but 1's out of it.
Cheers!
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |