Dynamic ranking, based on selection

[SDSolutions]

Hello,

I need to generate a visualisation that shows the Selectedvalue and the three neighboring values above and below the current selection. I have hundred of Agents, which are ranked by their value as in the table below. 

When the user selects an agent from a slicer, e.g. the row in green, I want the resulting visualisation to show the ranked neighbours plus or minus three around the selection.

UPDATE: Please note that RankbyValue is actually a Measure, calculated as follows:
RankbyValue = 
RANKX ( 
    ALL ( Agents[Agent] ),
    [_Sum Value],
    ,
    DESC, 
    DENSE 
)

Agent	Value			RankbyValue	DesiredResult
E	900			1
D	850			2	2
B	800			3	3
C	750			4	4
A	700			5	5
F	650			6	6
G	600			7	7
J	550			8	8
K	500			9
I	450			10

The optimal result would filter a visualisation based on the user's selection of agent and only showing the desired result row items.
The problem is similar to the question below. But I am unable to download PBIX and I am not sure if the syntax is optimized for a current version of PBI
https://community.fabric.microsoft.com/t5/Desktop/Measure-to-include-current-selection-and-rows-abov...

Any help would be greatly appreciated.

[johnt75 · Accepted solution]

First create a disconnected table containing all the agents, for use in the slicer. You can use something like

Agents for slicer = DISTINCT( Agents[Agent] )

Make sure that there is no relationship from this new table to the Agents table.

Create a measure like

Agent is visible =
VAR BaseRank =
    CALCULATE (
        [Rank By Value],
        TREATAS ( VALUES ( 'Agents for slicer'[Agent] ), Agents[Agent] )
    )
VAR CurrentRank = [Rank By Value]
VAR Result =
    IF ( CurrentRank >= BaseRank - 3 && CurrentRank <= BaseRank + 3, 1 )
RETURN
    Result

Add this as a filter to your table / matrix visual, set to show only when the value is 1.

[SDSolutions]

Thanks. That works as needed.

[SDSolutions]

Hi I have updated the topic as the siutation is a bit more complicated than my example. The RankbyValue is a measure as I need the ranking to be dynamic. 

[v-echaithra]

Hi @SDSolutions ,

Thank you for posting in Microsoft Community.

Create a measure to get the selected agent's rank, next create a measure to filter 3 Neighbors upside down like

Measure 1:
SelectedagentRank =
CALCULATE(
SELECTEDVALUE('Agents'[RankbyValue]),
ALLSELECTED('Agent')
)

Measure 2:
Three_values =
VAR Selectedrank = [Selectedagentrank]
VAR Currentrank = MAX('Agents'[Rankbyvalue])
RETURN
IF(
ABS(Currentrank - Selectedrank) <= 3,
1,0)

Now use this measure as a Visual level filter on the visual.

If this post helps, please give us Kudos and consider marking it Accept as solution to assist other members in finding it more easily.

Regards,
Chaithra