Pull Column from Unrelated Table

[TCatron18]

I have two tables that don't have a direct connection, but they are both connected a table that has a full list of all Users. I'm needing to pull a column from one of the tables into the other based on certain conditions.

Users Table

User

1

2

3

Term Table

User	Date
1	5/23/2024
2
3	9/9/2022

Start Table

User	Date
1	2/1/2023
2	4/7/2024
3	5/6/2025

I want to pull the Term[Date] into the Start table, but only if Term[User] = Start[User] and Term[Date] >= Start[Date].

I've tried using LOOKUPVALUE, but have not had any success getting it to only pull in if it meets the conditions.

[SamsonTruong · Accepted solution]

Hi @TCatron18 ,

I was able to create a calculated column for Term Date in the Start Table using the following DAX:

Term Date = 
VAR CurrentUser = [User]
VAR CurrentStartDate = [Date]
RETURN
    CALCULATE(
        MIN(Term[Date]),
        FILTER(
            Term,
            NOT ISBLANK(Term[Date]) &&
            Term[Date] >= CurrentStartDate
        ),
        TREATAS({CurrentUser}, Term[User])
    )

Using this calculated column with your test data, I get the following output:

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

[techies · Accepted solution]

Hi @TCatron18 please try this

MatchedTermDate =

CALCULATE(

    MIN('term table'[Date]),

    FILTER(

        'term table',

        'term table'[User] = 'start table'[User] &&

        NOT(ISBLANK('term table'[Date])) &&

        'term table'[Date] >= 'start table'[Date]

    )

)

[SamsonTruong · Accepted solution]

Hi @TCatron18 ,

I was able to create a calculated column for Term Date in the Start Table using the following DAX:

Term Date = 
VAR CurrentUser = [User]
VAR CurrentStartDate = [Date]
RETURN
    CALCULATE(
        MIN(Term[Date]),
        FILTER(
            Term,
            NOT ISBLANK(Term[Date]) &&
            Term[Date] >= CurrentStartDate
        ),
        TREATAS({CurrentUser}, Term[User])
    )

Using this calculated column with your test data, I get the following output:

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson