cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jtownsend21 Member
Member

Dynamic Column based on Slicer Selection

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: 


Employee Table.PNG

 

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

Invoice Table.PNG

 

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: 

 

Results.PNG

 

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Dynamic Column based on Slicer Selection

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.

 

output.PNG

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: Dynamic Column based on Slicer Selection

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.

 

output.PNG

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.