Dynamic Column based on Slicer Selection

[jtownsend21]

I think this is a really simple problem, unfortunately I just don't know how to solve it. 

I essentially have 2 tables. The first table is my User Table and it is laid out as follows: 

Then I have a Revenue table which is laid out like so: 

The goal is to have a slicer (or filter) from the employee table such that when I select an employee it will give me the total amount for that employee whether they are the Account Manager or Consultant. For instance if I select Tracy, it would give me the following: 

I have attempted to use a combination of a measure and a column to accomplish this. 

The measure would always show the selected employee's name when filtered as follows (this works perfectly)

Dynamic Employee Measure = 
IF(
    ISFILTERED('Users Table'[Employee Name]),
    MAX('Users Table'[Employee Name]),
    "No Employee Selected"
)

The column would then look for that name in either the Account Manager column or the Consultant column and return that name. The relationship between the Revenue Table and the User Table is based on this column. This one is the one that isn't working. 

Dynamic Employee Column = 
IF(
    OR(
    [Dynamic Employee Measure] = Revenue[Consultant],
    [Dynamic Employee Measure] = Revenue[Account Manager]),
    [Dynamic Employee Measure],
    "No Employee Selected"
)

Unfortunately, I can't get it to say anything besides the "No Employee Selected"

Thank you in advance for your help! 

[v-piga-msft · Accepted solution]

Hi @jtownsend21 ,

You should not create the relationship between the two tables and then create the measure with the formula below.

Measure =
CALCULATE (
    SUM ( 'Table2'[Amount] ),
    FILTER (
        'Table2',
        'Table2'[Account Manager] = SELECTEDVALUE ( Table1[Employee] )
            || 'Table2'[Consultant] = SELECTEDVALUE ( Table1[Employee] )
    )
)

Here is the ouptut.

Best  Regards,

Cherry