Forum Discussion

SDSolutions's avatar
SDSolutions
Frequent Visitor
1 year ago
Solved

Dynamic ranking, based on selection

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 ...
Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    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.

v-echaithra's avatar
v-echaithra
Community Support
1 year ago

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