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
Fabric Community Update - July 2025
Find out what's new and trending in the Fabric community.
Power BI Monthly Update - July 2025
Check out the July 2025 Power BI update to learn about new features.
