Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

LOOKUPVALUE - Return a value closest to a date in another table

Hi There, 

 

Aim: Create a Calculated Column that returns the value closest to the test date. 

 

I have a 'Growth' table (Table 1) and a 'Sprint' table (Table 2). The growth table contains the a Bio Age value. I would like to append Bio Age into the Sprint table in order to figure out what a player's Bio Age was at the time they took a sprint test. A player could have undertake a sprint test at a different time to getting their measurements taken (which are stored in the Growth table). Therefore, I need to find the Bio Age of a player that has been recorded closest to the session date that the sprint test was undertaken. 

 

Relationship: There is no relationship between the two tables as there are duplicate values within the two - which is why I thought LOOKUPVALUE may be the best method of finding the value. PlayerID is present in both tables.


Table 1 - Growth: 

Player IDSession DateHeight (cm)Bio Age
00101/04/2019130.78.8
00118/06/2020133.79.3
00109/03/2021134.99.7


Table 2 - Sprint: 

Player IDSession DateSprint Time - 5mBio Age
00101/05/20193.21?
00110/05/20203.18?
00124/05/20213.17?

 

Any help would be greatly appreciate. Many thanks in advance. 

calculated column
DAX
LOOKUPVALUE
Tabular Model
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

    Add the following column to the Growth Table:

    Closest Bio Age= 
var __player = Sprint[Player ID]
var __sprdate = Sprint[Session Date] 
var __growth = 
    ADDCOLUMNS(
        FILTER(
            Growth,
            Growth[Player ID] = __player
        ),
        "diff", ABS(DATEDIFF(__sprdate , Growth[Session Date] , DAY ))
    )
var __mindiff = MINX( __growth,[diff])

var __age =     
    MAXX(
        FILTER( __growth, [diff]  = __mindiff ),
        Growth[Bio Age]
    )
return
    __age

3 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    Anonymous 

    Add the following column to the Growth Table:

    Closest Bio Age= 
var __player = Sprint[Player ID]
var __sprdate = Sprint[Session Date] 
var __growth = 
    ADDCOLUMNS(
        FILTER(
            Growth,
            Growth[Player ID] = __player
        ),
        "diff", ABS(DATEDIFF(__sprdate , Growth[Session Date] , DAY ))
    )
var __mindiff = MINX( __growth,[diff])

var __age =     
    MAXX(
        FILTER( __growth, [diff]  = __mindiff ),
        Growth[Bio Age]
    )
return
    __age
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      So far so good! You my friend are a dream!

      Thank you so much for your input ðŸ˜€

    • fingalbrad's avatar
      fingalbrad
      Icon for Helper I rankHelper I
      4 years ago

      How would you  change the code if Player ID didnt exist?? and session date was just a numerical field and you had to look up and return the Bio Age based on the closest value to the numerical field?