Two dropdown menus to select two columns and calculate the difference/correlation

[kc_1116]

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

https://limewire.com/d/997fa681-3d66-4657-a310-6a6901ef5466#iQGXUyBikm-_s5Ox-hS2APKQ5G5LUEo7NrGi5-r2...

Can you point me to the right direction please? Thanks a million.

[OwenAuger · Accepted solution]

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?

[kc_1116]

May I ask one more question about the use of VALUES() in the following DAX?

Slope = 
VAR RegressionResult =
    LINESTX (
        VALUES ( Days[Day] ),
        [Score Person 2],
        [Score Person 1]
    )...

If I replace VALUES(Days[Day]) with Data like the following, the calculation of Slope will be wrong and [Score Person 2] seems to always return zeros.

Slope = 
VAR RegressionResult =
    LINESTX (
        Data,
        [Score Person 2],
        [Score Person 1]
    )...

Thanks again.

[OwenAuger]

Sure thing 🙂

LINESTX is an iterator. The first argument provided to LINESTX is a table, and the 2nd and 3rd+ arguments will be evaluated for each row of that table.

In practice, the table provided for the first argument represents the granularity of data points for which you want to perform the regression calculation. If we want data points per Day, we should iterate over the values of the dimension column Days[Day]. 

In my suggested measure, I used VALUES ( Days[Day] ) as the first argument. This expression returns a single-column table containing the distinct values of Days[Day]:

Day

1

2

3

LINESTX then evaluates Score Person 1 and Score Person 2 for each row of this table. You can think of this operation as each value of Day from 1 to 3 being applied as a filter in turn, and the two scores evaluated with that filter applied.

Assuming Person 1 = "Bill" and Person 2 = "David", LINESTX is working with these values:

Days[Day]	Score Person 1	Score Person 2
1	2	3
2	3	2
3	4	1

You can check these values by running this query using ADDCOLUMNS (also an iterator) in DAX query view:

EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
        VALUES ( Days[Day] ),
        "Score Person 1", [Score Person 1],
        "Score Person 2", [Score Person 2]
    ),
    'Person 1'[Person 1] = "Bill",
    'Person 2'[Person 2] = "David"
)

Note that any rows where both Score Person 1 and Score Person 2 are blank would actually be ignored by LINESTX.

Back to your question: If you instead use Data as the first argument of LINESTX, things go wrong because of the way filters interact, and the calculation of Scores and regression don't work as intended.

If you run this DAX query to visualize the data LINESTX is working with, it becomes clearer:

EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
        Data,
        "Score Person 1", [Score Person 1],
        "Score Person 2", [Score Person 2]
    ),
    'Person 1'[Person 1] = "Bill",
    'Person 2'[Person 2] = "David"
)

Data[Day]	Data[Person]	Data[Score]	[Score Person 1]	[Score Person 2]
1	Bill	2	2	(blank)
2	Bill	3	3	(blank)
3	Bill	4	4	(blank)

The main issue here is that by iterating over the table Data (the fact table), the Score measures are evaluated with rows of the fact table applied as filters. The Score Person 1 and Score Person 2 measures are written with the intention of filters being applied to columns of dimension tables: Days, Person 1 and Person 2.

• Since we have an existing filter Person 1 = "Bill", the rows of the fact table are limited to those where Data[Person] = "Bill". This happens to be fine for Score Person 1 but not Score Person 2.
• Score Person 2 removes filters on Person 1 and activates the relationship between Person 1 and Person 2, but this conflicts with the filter Data[Person] = "Bill" coming from context transition when Score Person 2 is evaluated in each row of this table.
• Another potential problem: We don't know in advance if there are multiple rows in Data for each Day/Person combination. Bill could have two rows on Day 1 which should be summed to a single value per day (as with the original measure), not treated as separate data points (as with this version of the measure).

The key thing to remember is that when using LINESTX or any iterator function, the table argument determines the "granularity" of the calculation. Expressions evaluated by the iterator will be evaluated once per row of the table argument provided.

I realise there were a few points involved in that explanation. Hopefully that makes sense 🙂

[kc_1116]

After studying your reply and reading the DAX Definitive Guide, I believe my wrong DAX is probably translated into the following code by the DAX engine due to context transition as the row context is invalidated by the implicit surrounding CALCULATE when a measure is used.

VAR RegressionResult =
    LINESTX (
        -- VALUES ( Days[Day] ),	// This is correct
		Data,						// This is wrong
        CALCULATE(					// Context transition
			SUM(Data[Score]),
			Data[Person] = "Bill"
		),
        CALCULATE(
			SUM(Data[Score]),
			REMOVEFILTERS ( 'Person 1' ),
    		USERELATIONSHIP ( 'Person 1'[Person 1], 'Person 2'[Person 2] ),
			Data[Person] = "Bill",	// Conflict
			Data[Person] = "David"	// Conflict
		)
    )
VAR Result =
    SELECTCOLUMNS ( RegressionResult, [Slope1] )
RETURN
    Result

[kc_1116]

Thanks so much for solving the problem and explaining everthing in details. You made my day 😀