Forum Discussion

drivas771994's avatar
drivas771994
Icon for Helper II rankHelper II
4 years ago
Solved

How to pull most recent data based on a a data value from another table.

Hi, I'm trying to pull data from one table based on the relationship to a date from another table. Here's the example.   I have two tables called tests and scans. In the test table i have test res...
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi drivas771994 ,

    According to your description, you have two tables, SPARTA_SCANS2 and TESTS, I create a sample.

    Test table:

    SPARTA_SCANS2 table:

    You want to get the Body weight of the latest Test Date which are greater than current Scan Date.

    Here's my solution.

    Most Recent Body Mass =
MAXX (
    FILTER (
        ALL ( 'TESTS' ),
        'TESTS'[Date]
            = MINX (
                FILTER ( ALL ( 'TESTS' ), 'TESTS'[Date] >= MAX ( 'SPARTA_SCANS2'[Date] ) ),
                'TESTS'[Date]
            )
    ),
    'TESTS'[Bodyweight]
)

    Get the correct result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

You could create a measure like

Most Recent Body Mass =
var currentAthlete = SELECTEDVALUE('Scans'[Athlete])
var currentDate = SELECTEDVALUE('Scans'[Date])
return SELECTCOLUMNS(
CALCULATETABLE( TOPN( 1, 'Tests', 'Tests'[Date]),
'Tests'[Athlete] = currentAthlete && 'Tests'[Date] <= currentDate),
"@val", 'Tests'[Body weight]
)