how to create a custome coulumn in one table based on an unrelated table in the model

[Anonymous]

Hi,

I have the following model:

What I need to do is to create a custom column in BAR PROJECT_GROUP_PROJCT using POJECT_OWNER column, and relate it to dbo Sales Rep table using Rep ID - Name_c custom column. (there is no direct relationship between the 2 table as seen in the model).

The only thing in comon in values of each 2 column are the left 2 characters of the value (which basically is the Rep ID). 

the values of the two columns are as below:

So I need the custom column that I create in PROJECT GROUP table to have the same values as column 
Rep ID - Name_c (which is a combination of Rep ID and Name of the Reps).

I cannot change the model. It ruins all my dashboard I guess.

I hope someone can help me.

[Mariusz]

Hi @Anonymous,

Try this column.

Rep ID - Name_c = 
VAR _ProjectOwner = 'BAR PROJECT_GROUP_PROJCT'[PROJECT_OWNER]
RETURN 
CALCULATE(
    SELECTEDVALUE( 'Sales Rep table'[Rep ID - Name_c] ), 
    LEFT( 'Sales Rep table'[Rep ID - Name_c], 2 ) = _ProjectOwner
) 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Mariusz]

Hi @Anonymous,

Try this column.

Rep ID - Name_c = 
VAR _ProjectOwner = 'BAR PROJECT_GROUP_PROJCT'[PROJECT_OWNER]
RETURN 
CALCULATE(
    SELECTEDVALUE( 'Sales Rep table'[Rep ID - Name_c] ), 
    LEFT( 'Sales Rep table'[Rep ID - Name_c], 2 ) = _ProjectOwner
) 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.