How to add a column from another table with inactive relationship

[Gabiiiii]

There are 2 tables in this particular case and they have an inactive 1-m relationship. (because the data model are more complicated, cannot make the relationship active)

The fact table looks like this:

The dimention table(Business Unit) is like this:

I want to add BU_name into the fact table accordingly, and I use:

CALCULATE(LASTNONBLANK(BusinessUnit[BU_name],BusinessUnit[BU_name]),USERELATIONSHIP(Fact_table[BU_id],BusinessUnit[BU_id]))

but it returns the wrong value. 

Can someone help with this problem? Thank you so much!!

[Aditya_Meshram]

Lookupvalue() can also be used in this case which does not depend on relationships in the model.
Column = 
LOOKUPVALUE( Dim[BU_name],Dim[BU_id],fact[BU_id])

[Samarth_18 · Accepted solution]

Hi @Gabiiiii ,

Create a column as below:-

Column =
CALCULATE (
    LASTNONBLANK ( Dim[BU_name], Dim[BU_name] ),
    FILTER ( Dim, 'Fact'[BU_id] = Dim[BU_id] ),
    USERELATIONSHIP ( 'Fact'[BU_id], Dim[BU_id] )
)

BR,

Samarth

[leo006]

I think most people assume that FILTER is automatically applied as the relationship was set though its inactive.

Thanks Samrath, after adding the FILTER, i am getting the correct value.

[Gabiiiii]

Thank you Samarth!