Forum Discussion

TCatron18's avatar
TCatron18
Helper II
1 year ago
Solved

Pull Column from Unrelated Table

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

UserDate
15/23/2024
2 
39/9/2022

 

Start Table

UserDate
12/1/2023
24/7/2024
35/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.

Need Help
  • SamsonTruong's avatar
    SamsonTruong
    1 year ago

    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's avatar
    techies
    1 year ago

    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]
        )
    )

2 Replies

  • techies's avatar
    techies
    Super User
    1 year ago

    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's avatar
    SamsonTruong
    Super User
    1 year ago

    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