Forum Discussion
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 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-above-and-below/m-p/475055
Any help would be greatly appreciated.
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 ResultAdd this as a filter to your table / matrix visual, set to show only when the value is 1.
4 Replies
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,
ChaithraHi 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.
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 ResultAdd this as a filter to your table / matrix visual, set to show only when the value is 1.
Thanks. That works as needed.
