Forum Discussion
Rank Based on Selected Data from Scatterplot
I have pulled some data via DirectQuery. The two tables are "asum" and "po". There is a one-to-many relationship from asum to po. I use asum to make a scatter plot. For example, if I query ID = 5, then I get the subsets of asum and po with ID = 5. Then I use X1 as the x values and Y as the y values for the scatter plot. I have a simple table for po. I want a measure that ranks the Option column based on selected values in the scatter plot. So when the scatter plot is unflitered (no selection) the rank on the Option is just the Option. Then for example if I select Options 2, 3, and 5 in the scatter plot, the ranks will be 1, 2, 3 respectively. I want this desired rank for both asum and po (po has duplicate Options). Seems simple but I cannot figure it out. How can I accomplish this?
Table asum
| ID | Option | X1 | X2 | Y | Mean |
| 4 | 1 | 151 | 55 | 2100 | 53 |
| 4 | 2 | 85 | 31 | 981 | 30 |
| 4 | 3 | 196 | 72 | 1978 | 68 |
| 5 | 1 | 103 | 38 | 1431 | 36 |
| 5 | 2 | 179 | 65 | 2054 | 62 |
| 5 | 3 | 159 | 58 | 1605 | 55 |
| 5 | 4 | 161 | 59 | 1464 | 56 |
| 5 | 5 | 232 | 85 | 1929 | 81 |
Table po
| ID | Option | Value | Level |
| 4 | 1 | 50 | 0 |
| 4 | 1 | 700 | 1 |
| 4 | 1 | 1750 | 2 |
| 4 | 1 | 1750 | 2 |
| 4 | 1 | 2500 | 3 |
| 4 | 2 | 50 | 0 |
| 4 | 2 | 4950 | 1 |
| 4 | 3 | 50 | 0 |
| 4 | 3 | 700 | 1 |
| 4 | 3 | 700 | 1 |
| 4 | 3 | 4250 | 2 |
| 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 result on asum when Options 2, 3, 5 are selected on scatter plot
Desired result on po when Options 2, 3, 5 are selected on scatter plot
Hi user01 ,
Ashish_Mathur mentioned the relationships , and I will continue to add to it later in this section:
Below is my table1:
Below is my table2:
The following DAX might work for you:
Rank asum = VAR SelectedOptions = SELECTEDVALUE('asum'[Option]) RETURN RANKX( ALLSELECTED('asum'), CALCULATE(MAX('asum'[Option])), , ASC, DENSE ) Rank po = VAR SelectedOptions = SELECTEDVALUE('po'[Option]) RETURN RANKX( ALLSELECTED('po'), CALCULATE(MAX('po'[Option])), , ASC, DENSE )The final output is shown in the following figure:
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi,
Which columns have you used in both tables for the 1 to Many relationship? I do not see a way to do that because ID and Options in both tables have duplicates.
Sorry for being unclear. It is asum Option (one) to po Option (many). The SQL Server has all the data, but I just query one ID (eg. ID = 5) into Power Bi via Direct Query. So asum[Option] looks like it has no duplicates.
Your reply does not answer my question.
Hi user01 ,
Ashish_Mathur mentioned the relationships , and I will continue to add to it later in this section:
Below is my table1:
Below is my table2:
The following DAX might work for you:
Rank asum = VAR SelectedOptions = SELECTEDVALUE('asum'[Option]) RETURN RANKX( ALLSELECTED('asum'), CALCULATE(MAX('asum'[Option])), , ASC, DENSE ) Rank po = VAR SelectedOptions = SELECTEDVALUE('po'[Option]) RETURN RANKX( ALLSELECTED('po'), CALCULATE(MAX('po'[Option])), , ASC, DENSE )The final output is shown in the following figure:
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
