Hi,
I am looking to create two dropdown menus to select two arbitary persons from the data set and calculate the difference in each day or the correlation.
The original dataset in in wide format. Mindful that it is not the ideal format in Power BI, I have unpivot it into a long format.
| Day | Anne | Bill | Charles | David | Evan |
| 1 | 1 | 2 | 2.5 | 3 | 3 |
| 2 | 2 | 3 | 2.5 | 2 | 1 |
| 3 | 3 | 4 | 2.5 | 1 | 2 |
However, I struggled to create a calculate the score difference between two person in each day. I tried to use do so by using NATURALJOIN and SELECTEDVALUE, but SELECTEDVALUE always return empty instead of what is selected in the dropdown menu.
Please find below the link to my Power BI file
Can you point me to the right direction please? Thanks a million.
Hi @kc_1116
For pairwise comparison calculations, I would suggest setting up the model in a similar way to the "Comparing different time periods" pattern.
I have attached your PBIX modified to use this method.
This is how I would suggest setting up the model:
- This avoids duplicating the Data table.
- Measure values based on filters on 'Person 2' rely on DAX to remove filters on 'Person 1' and activate the relationship between 'Person 1' and 'Person 2' (see below).
You can then write measures like these:
Score Person 1 =
SUM ( Data[Score] )Score Person 2 =
CALCULATE (
[Score Person 1],
REMOVEFILTERS ( 'Person 1' ),
USERELATIONSHIP ( 'Person 1'[Person 1], 'Person 2'[Person 2] )
)Variance Person 1 vs Person 2 =
[Score Person 1] - [Score Person 2]Coefficient of Determination =
VAR RegressionResult =
LINESTX (
VALUES ( Days[Day] ),
[Score Person 2],
[Score Person 1]
)
VAR Result =
SELECTCOLUMNS ( RegressionResult, [CoefficientOfDetermination] )
RETURN
Result
Note: For the measures using LINESTX, (Coefficient of Determination, Slope and Intercept), I treated Score Person 2 as the dependent variable and Score Person 1 as the independent variable. You may well want to switch these around.
Would something like this work for you?
Hi @kc_1116
For pairwise comparison calculations, I would suggest setting up the model in a similar way to the "Comparing different time periods" pattern.
I have attached your PBIX modified to use this method.
This is how I would suggest setting up the model:
- This avoids duplicating the Data table.
- Measure values based on filters on 'Person 2' rely on DAX to remove filters on 'Person 1' and activate the relationship between 'Person 1' and 'Person 2' (see below).
You can then write measures like these:
Score Person 1 =
SUM ( Data[Score] )Score Person 2 =
CALCULATE (
[Score Person 1],
REMOVEFILTERS ( 'Person 1' ),
USERELATIONSHIP ( 'Person 1'[Person 1], 'Person 2'[Person 2] )
)Variance Person 1 vs Person 2 =
[Score Person 1] - [Score Person 2]Coefficient of Determination =
VAR RegressionResult =
LINESTX (
VALUES ( Days[Day] ),
[Score Person 2],
[Score Person 1]
)
VAR Result =
SELECTCOLUMNS ( RegressionResult, [CoefficientOfDetermination] )
RETURN
Result
Note: For the measures using LINESTX, (Coefficient of Determination, Slope and Intercept), I treated Score Person 2 as the dependent variable and Score Person 1 as the independent variable. You may well want to switch these around.
Would something like this work for you?
