Adding a MEASURE to a TABLE

[Anonymous]

Looking for assistance!

We are trying to use the difference between; TestID 2, V1 and TestID 1 V1 in Table1, as the Z value for V1 in Table2.

In Table1 I have some data from a test. 

We have created measures to return the differnce between TestID 2, V1 and TestID 1 V1 as well as TestID 2, V2 and TestID 1 V2, they return the below.

Measure 

V1 DELTA =

VAR FIRST = MIN(Table1[TestID])

VAR vid1 = CALCULATE(AVERAGE(Table1[V1]), Table1[TestID]=FIRST)

var last = MAX(Table1[TestID])

var vid2 = CALCULATE(AVERAGE(Table1[V1]), Table1[TestID]=last)

return vid2-vid1

We would like to be able to use the values returned from the measures of V1 DELTA and V2 DELTA as the Z value in the below table. We have tried several paths but are still stuck. 

Table2 represents the location of V1 and V2 on a map, we would like to use the DELTA measure to describe the Z value.

Thanks for any help provided!

[TeigeGao · Accepted solution]

Hi @Anonymous ,

According to your description, my understanding is that you are going to put the difference of V1 (Last one - Top one) to the set V1-Z.

Assume that we have the following two tables:

Then we can create a measure like below:

Z =
IF (
    MIN ( Table2[VAR] ) = "V1",
    CALCULATE ( MIN ( Table1[V1] ), TOPN ( 1, Table1, Table1[TestID], DESC ) )
        - CALCULATE ( MIN ( Table1[V1] ), TOPN ( 1, Table1, Table1[TestID], ASC ) ),
    CALCULATE ( MIN ( Table1[V2] ), TOPN ( 1, Table1, Table1[TestID], DESC ) )
        - CALCULATE ( MIN ( Table1[V2] ), TOPN ( 1, Table1, Table1[TestID], ASC ) )
)

The result will like below:

Best Regards,

Teige

[Anonymous]

@TeigeGao 

Thanks, this worked great!