Forum Discussion
Helper IDynamic filter with rankings
Hello,
I have a dataset similar to the below:
| Colour | Shade | Value | Rank |
| Red | Light | 8000 | 8 |
| Blue | Light | 10000 | 10 |
| Green | Light | 4000 | 4 |
| Orange | Light | 5000 | 5 |
| Purple | Light | 2000 | 2 |
| White | Light | 9000 | 9 |
| Brown | Light | 3000 | 3 |
| Black | Light | 6000 | 6 |
| Violet | Light | 7000 | 7 |
| Yellow | Light | 1000 | 1 |
| Red | Dark | 14 | 2 |
| Blue | Dark | 35 | 5 |
| Green | Dark | 21 | 3 |
| Orange | Dark | 49 | 7 |
| Purple | Dark | 42 | 6 |
| White | Dark | 56 | 8 |
| Brown | Dark | 63 | 9 |
| Black | Dark | 7 | 1 |
| Violet | Dark | 28 | 4 |
| Yellow | Dark | 63 | 9 |
The 'rank' column is ranking each colour based on the 'value' column, but is specific to the shade. So for blue it is ranked 10 for light, and 5 for dark.
I am trying to create a chart that allows the user to be able to select a colour, say blue, and show a line/bar chart with the value of blue for the shade they have filtered to (light/dark), plus the nearest 2 rankings above and below the colour the user has selected.
So if I selected blue and had the page flitered to the shade dark, I would expect to see a chart which contains the below rows in a visual like a line/bar chart:
| Colour | Shade | Value | Rank |
| Green | Dark | 21 | 3 |
| Belgium | Dark | 28 | 4 |
| Blue | Dark | 35 | 5 |
| Purple | Dark | 42 | 6 |
| Orange | Dark | 49 | 7 |
I am struggling identifying a way to do this which does not result with me having the chart filtered to 'blue' and nothing else, so any advice would be greatly appreciated!
2 Replies
- amitchandak
Super User
Scocal123 , Try a measure like
Rankx(filter(all(Table[Colour], Table[Shade]), Table[Shade] = max(Table[Shade])), calculate(Sum([Value])), ,asc,dense)
Scocal123Thanks for the response! Unfortunately this doesn't appear to produce the outcome. I think this may be due to my poor description, apologies.
I believe I am looking for a measure that will allow me to have 'Colour' on the X-axis of a bar chart, and the sum of the 'value' column on the Y-axis. The visual will have a filter on it to specify whether the chart is showing light/dark from the 'shade' column.
The problem is that for this measure, when the user selects the colour 'Blue' in a slicer, for example, I need the chart to show me the Sum value of the colour 'Blue', plus the 2 closest ranked values for that shade. For example, my chart would have the below values in it as a result of the measure where the visual is filtered to the shade 'dark':Colour Shade Value Rank Green Dark 21 3 Belgium Dark 28 4 Blue Dark 35 5 Purple Dark 42 6 Orange Dark 49 7
Hope this makes sense & thanks for the help
