Value associated with a Max in a related table, with grouping

[jeffk4575]

Hello experts!

I have two tables: a transaction table with a deviceID, timestamp and a value, like this:

DeviceID         TimeStamp               Value  

A                     3/1/16 7:00               97

A                     3/1/16 8:37               91

B                     3/1/16 15:17             94

D                     3/1/16 2:12               80

D                     3/2/16 2:10               90

D                     3/3/16  2:13              87

The second table is a master data table with DeviceID information. The two tables have a relationship based on DeviceID. I would like to add the column "Current Value" to the table, based on the most recent (max) TimeStamp.

DeviceID                 Customer                   Current Value

A                             Joe's Store                 91

B                             Gas station 2              94

C                             Market 3

D                             Store 4                        87 

Note that there could be more than one transaction per day (so I think I need to use a max function, not a date function). And there can be some devices with no transaction data (like DeviceID C). 

I've been able to pull the latest TimeStamp into the Device ID table, and I've been able to use related tables to get the averagex of the values for a particular device, but I can't seem to get only the current value. I'm missing something small. Any help would be appreciated!

Thank-you,

JK

[Sean · Accepted solution]

I would just add  " + 0 " at the end. Otherwise C would not show up in Visualizations!

[pvarley]

I am trying to apply this pattern to a very similar problem, and it is not working for me. Like JK, I have two related tables. One contains customer information and the other contains subscription details. The subscription table has zero or more entries for each customer. 

The customer table looks something like this:

Name    CustomerID

A           100

B           101

C           102

And the subscriptions table looks like this:

CustomerID   StartDate      EndDate     SKU

100                 4/1/2015      3/31/2016  Y-34

100                 4/1/2016      3/31/2017  Y-34

100                 4/1/2017      3/31/2018  Y-30

101                 6/28/2016    6/27/2017  Y-12

101                 6/28/2016    7/27/2016  Y-12

I added a calculated column to the subscriptions table indicating whether the subscription is active today. This is important because there are future subscriptions such as the third entry in the table above that have not started yet.

I also have a measure that calculates the active subscription with the latest end date.

MaxActiveExpirationDate:=CALCULATE( MAX( Subscriptions[EndDate] ),
Subscriptions[IsActive] = TRUE
)

I've tried to combine this with your formula as follows:

= CALCULATE(
LOOKUPVALUE(Subscriptions[SKU],
Subscriptions[EndDate], [MaxActiveExpirationDate] )
)

Excel gave an error saying there are multiple values, so I added the second condition.

= CALCULATE(
LOOKUPVALUE(Subscriptions[SKU],
Subscriptions[EndDate], [MaxActiveExpirationDate] ),
Subscriptions[EndDate], VALUES( Subscriptions[EndDate] )
)

It is possible that there are multiple subscriptions for the same customer with the same end date, and I thought that's what you were solving with the VALUES condition, but I confess that I do not understand how it's supposed to work. As I understand it VALUES returns a one-column table with the distinct values it finds, but we should be looking for a single value here. 

[Vvelarde]

If there are multiple subscriptions with same end date; what result you expected?

Example:

Customer ID EndDate       SKU

100                01/01/2017  AA1

100                01/01/2017  AA2

100                01/01/2017  AA3

[pvarley]

In practice, when there are multiple subscriptions with the same end date, the SKU will normally be the same. If they are not, I would be happy to pick one arbitrarily, say the one that has the greatest value.

[jeffk4575]

You might try something like this: create a new column in the customer table, using the formula below.

The filter, related table first links the subscriptions table to the customer table, then filters for active subscriptions = 1. Finally, it returns the Max date across the filtered records, using the "x" function, maxx. 

Last_Subscription = maxx(filter (relatedtable(Subscriptions),Subscriptions[IsActive] = 1), Subscriptions[EndDate])

It might not work exactly as desired, but it should point you in the right direction.

jk

[pvarley]

Hi JK - That works to get the latest end date of an active subscription for the customer, but I want the SKU for the active subscription with the latest end date. I have confirmed that there are subscriptions in the data for the same customer with the same end date, which is why Excel gives the error.

[pvarley]

I think I have it figured out now. I used the Remove Duplicates feature in Power Query to remove rows that have the same Customer ID, Start Date and End Date for the subscriptions. In practice, these will always have the same SKU in my data set, and even if they didn't I'll take an arbitrary row.

Next I added another test in the LOOKUPVALUE formula. I'm looking for the subscription with the earliest start date in addition to the one with the latest end date. When subscriptions overlap in this data set, one almost always fits within the other, so I'm choosing the longer one and getting its SKU.

= CALCULATE( LOOKUPVALUE( Subscriptions[Sku],

    Subscriptions[EndDate], [MaxActiveExpirationDate],

    Subscriptions[StartDate], [MinActiveStartDate],

    Subscriptions[CustomerID], VALUES( Subscriptions[CustomerID] )

    )

    )

And now I think I understand the use of VALUES in the last clause. At first I thought that CALCULATE would translate the Customer ID on the row context of the calculated column into a filter context that LOOKUPVALUE would respect, but I'm guessing that this is not the case and we need to tell LOOKUPVALUE which CustomerID to look for. I'm not sure about this explanation, though, so perhaps Victor could confirm that or explain how it really works.

Thanks for your help.

Paul

[pvarley]

@Vvelarde provided a general-purpose solution to my original scenario that does not require de-duplication in Power Query. This is his solution. First he created a measure: 

MeasureName = CALCULATE (

    VALUES ( Subscriptions[Sku] ),

    FIRSTNONBLANK (

        TOPN (

            1,

            FILTER ( Subscriptions, Subscriptions[IsActive] = TRUE ),

            Subscriptions[EndDate]

        ),

        0

    )

)

Then he added a calculated column in the customer table as follows:

LOOKUPVALUE( Subscriptions[Sku], Subscriptions[Sku], [MeasureName] )

Thanks Victor!

[Vvelarde]

hi @pvarley

1: Create this measure:

Medida =
CALCULATE (
    VALUES ( Suscriptions[SKU] );
    FIRSTNONBLANK (
        TOPN (
            1;
            Suscriptions;
            CALCULATE ( MAX ( Suscriptions[EndDate] ); Suscriptions[IsActive] = TRUE () ); DESC
        );
        0
    )
)

2: Use the lookvalue in a calculated column in Customers Table:

SKU-LK =
LOOKUPVALUE ( Suscriptions[SKU]; Suscriptions[SKU]; [Medida] 

[Vvelarde]

Hi @jeffk4575

Use this calculated column in Devices Table:

Current Value = CALCULATE(LOOKUPVALUE('Transact-Devices'[Value];'Transact-Devices'[TimeStamp];MAX('Transact-Devices'[TimeStamp])))

[Sean · Accepted solution]

I would just add  " + 0 " at the end. Otherwise C would not show up in Visualizations!

[jeffk4575]

Works great. Thank-you!

[jeffk4575]

Thanks again for the help. I've run into another issue, that would likely NOT occur often in practice, depending on the precision of the timestamp. If there are identical values for the timestamp across different devices, the DAX expression returns an error, saying that "A table of multiple values was supplied where a single value was expected." You can see the example below. Device B and D have the same maximum value for timestamp. Any suggestions on how to improve the expression for this situation?

DeviceID	TimeStamp	Value
A	3/1/2016 7:00	97
A	3/1/2016 8:07	92
B	3/1/2016 7:00	94
D	3/1/2016 2:00	80
D	3/1/2016 2:04	90
D	3/1/2016 7:00	87

[Vvelarde · Accepted solution]

Hi @jeffk4575

this is fixed adding a second column search:

Current Value =
CALCULATE (
    LOOKUPVALUE (
        'Transact-Devices'[Value];
        'Transact-Devices'[TimeStamp]; MAX ( 'Transact-Devices'[TimeStamp] );
        'Transact-Devices'[DeviceID]; VALUES ( Devices[Device ID] )
    )
)
    + 0

[tslyonsnj]

Hi @Vvelarde...  thanks for this.  I just successfully adapted this solution to a requirement I've been wrestling with.  Could you expand for a determined learner on how a "second column search" does what's needed in this case?  I'm gratified that it works, but understanding what's going on under the covers would be very helpful.  Thanks. 

[jeffk4575]

Thanks for the help! Works great.

Jeff