I use DirectQuery to get two tables, asum and po. I use X and Y from asum to make a scatter plot. I also use asum and po to make two simple table visuals.
Scenario: I click on Options 2, 3, and 5 on the scatter plot (x-y cordinates: (179 ,2054) , (159 ,1605) , (232 ,1929)) . Then the asum table filters.
Goals:
1. I want to have a dynamic Index in asum where Options 2, 3, 5 have Index 1, 2, 3 respectively. (ie. The smallest Option filtered has Index 1, then next smallest has Index 2, etc.)
2. I want the po table to be filtered from the scatter plot selection so it only shows Options 2, 3, 5.
3. I want the po table to also have the same Index results as asum, but with duplicates.
(Desired results below)
Attempt:
I made the index with RANKX in asum. It seems to work.
Rankasum = RANKX(
ALLSELECTED(asum),
CALCULATE(MAX(asum[Option])),, ASC
)
Next, I tried to make a 1:Many relationship from asum[Option] to po[Option] and used a similar measure as above using the DENSE parameter. (I can remove the relationship if it is not necessary.)
For po table, the Index seems to work with no selection on the scatter plot, but does not work when Options are selected in the scatter plot. But the rows are filtered like I want.
For asum, a blank row is introduced with no selection, but seems to work when Options are selected in the scatter plot.
How can I can I achieve my goals above? I imagine RANKX is the way to go, but any other approach is appreciated.
Table asum
| ID | Option | X | Y |
| 5 | 1 | 103 | 1431 |
| 5 | 2 | 179 | 2054 |
| 5 | 3 | 159 | 1605 |
| 5 | 4 | 161 | 1464 |
| 5 | 5 | 232 | 1929 |
Table po
| ID | Option | Value | Level |
| 5 | 1 | 100 | 0 |
| 5 | 1 | 400 | 1 |
| 5 | 1 | 500 | 2 |
| 5 | 1 | 500 | 2 |
| 5 | 1 | 1500 | 3 |
| 5 | 2 | 100 | 0 |
| 5 | 2 | 2400 | 1 |
| 5 | 3 | 100 | 0 |
| 5 | 3 | 400 | 1 |
| 5 | 3 | 400 | 1 |
| 5 | 3 | 500 | 2 |
| 5 | 3 | 500 | 2 |
| 5 | 3 | 500 | 3 |
| 5 | 3 | 500 | 3 |
| 5 | 3 | 1000 | 4 |
| 5 | 4 | 100 | 0 |
| 5 | 4 | 400 | 1 |
| 5 | 4 | 400 | 1 |
| 5 | 4 | 500 | 2 |
| 5 | 4 | 500 | 2 |
| 5 | 4 | 500 | 3 |
| 5 | 4 | 500 | 3 |
| 5 | 4 | 500 | 4 |
| 5 | 4 | 500 | 4 |
| 5 | 4 | 500 | 5 |
| 5 | 5 | 100 | 0 |
| 5 | 5 | 400 | 1 |
| 5 | 5 | 400 | 1 |
| 5 | 5 | 500 | 2 |
| 5 | 5 | 500 | 2 |
| 5 | 5 | 500 | 3 |
| 5 | 5 | 500 | 3 |
| 5 | 5 | 500 | 4 |
| 5 | 5 | 500 | 4 |
| 5 | 5 | 500 | 5 |
| 5 | 5 | 500 | 5 |
Desired asum
Desired po
What do you mean by "index result"? Do you really need the rankx when you can sort by the Option column?
Helpful resources
Power BI Dataviz World Championships
The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!
Power BI Monthly Update - December 2025
Check out the December 2025 Power BI Holiday Recap!
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
