Forum Discussion
BhavaniK
9 months agoFrequent Visitor
Need help on Ranking within table.
Hi Team, I'm looking for some help to achieve the logic. Right now I have table with these columns Event, Number, Location, sum of delay imapact. in the visual filter I have added, Number-> Top 5...
- 9 months ago
It really depends on what you're expecting as an output.
Assumptions for the below:
- I used your sample data and created a table called 'sample'
- I created a measure called _delay = SUM('sample'[Delay])
Create this measure, put it in the visual filters where it is 1.
__rank = VAR __locations = ALLSELECTED('sample') VAR __ranked = ADDCOLUMNS( __locations , "__delay", [_delay] , "__rank", RANKX( __locations , [_delay] , BLANK() , DESC , Skip ) ) VAR __top = TOPN( 5 , __ranked , [__rank], ASC , [__delay], DESC , 'sample'[Location], ASC ) VAR __isTop = CONTAINS( __top , 'sample'[Location], SELECTEDVALUE('sample'[Location]) ) VAR __result = IF( __isTop , 1 , 0 ) RETURN __resultMy code is GitHub Copilot assisted, but always tested.
KNP
9 months agoSuper User
It really depends on what you're expecting as an output.
Assumptions for the below:
- I used your sample data and created a table called 'sample'
- I created a measure called _delay = SUM('sample'[Delay])
Create this measure, put it in the visual filters where it is 1.
__rank =
VAR __locations =
ALLSELECTED('sample')
VAR __ranked =
ADDCOLUMNS(
__locations
, "__delay", [_delay]
, "__rank", RANKX(
__locations
, [_delay]
, BLANK()
, DESC
, Skip
)
)
VAR __top =
TOPN(
5
, __ranked
, [__rank], ASC
, [__delay], DESC
, 'sample'[Location], ASC
)
VAR __isTop =
CONTAINS(
__top
, 'sample'[Location], SELECTEDVALUE('sample'[Location])
)
VAR __result =
IF(
__isTop
, 1
, 0
)
RETURN __result
My code is GitHub Copilot assisted, but always tested.