Lookup latest matching value from unrelated table

[AnhP]

I'm having 2 unrelated tables which I'm trying not to create relationship to avoid circular relationship.

Table 1: has [username]

Table 2: has [username], [business unit], [report month]. Table 2 contain appended data month after month of active users. i.e. all active users in Jan and then all active user in Feb.

I'm trying to add a new column to table 1 using DAX that return the latest business unit of an user. e.g return business unit in Feb, if that user is not active in Feb then return Business unit in Jan and so on.

please help. thank you

[tamerj1 · Accepted solution]

Hi @AnhP 
Please try

Latest business unit =
MAXX (
    TOPN (
        1,
        FILTER ( Table2, Table2[username] = Table1[username] ),
        Table2[report month]
    ),
    Table2[business unit]
)

[AnhP]

Hi @tamerj1 , Thanks for the suggestion.

The formula works with no error but it only return the Business Unit for employee active in the latest month (Feb). For employees active in previous months (Jan and prior) it's a blank. 
I don't know why because your formula make sense though.

[tamerj1]

@AnhP 
Strange. It should work. Can you provide a sample file or at least sample data to test? You can just share a download link. 

[AnhP]

Hi Tamerj, I can't share the data as it's confidential. I think it's rather a data issue. I'll do some more testing. Thanks,

[tamerj1]

@AnhP 
Perhaps attached sample file will help you out finding the source of the issue