Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
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 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?
I do not understand your question/data at all. Someone who does will help you.
User | Count |
---|---|
98 | |
90 | |
84 | |
70 | |
66 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |