Rank Based on Selected Data from Scatterplot

[user01]

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

[v-xiandat-msft · Accepted solution]

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.

[Ashish_Mathur]

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.

[user01]

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.

[Ashish_Mathur]

Your reply does not answer my question.

[user01]

Hi Ashish, Sorry for the confusion. I am using the Option column from both tables as 1:Many. I only have loaded the subsets where ID = 5. So there are no duplicates from in asum[Option]. Can we pretend ID = 4 does not exist?

[Ashish_Mathur]

I do not understand your question/data at all.  Someone who does will help you.