Using slicer to select a related value

[CarlsBerg999]

Hi,

I have a table with Employee names and Jobs:

Employee Name	Job
Todd	Analyst
Frank	Controller
Jessie	Analyst

Another table has Employee Names and total hours:

Employee Name	Job	Hours
Todd	Analyst	4
Todd	Analyst	6
Frank	Controller	4
Frank	Controller	9
Jessie	Analyst	3

There is a One-to-Many relationship between employee masterdata and the total hours data table. I want to create a filter that sums the hours for all Analysts when i click on Jessie's name on a slicer. What i've been trying is this:

Peer Group Logged Hours = CALCULATE(

SUM('Table2'[Hours]),

FILTER('Table2','Table2'[Job]=LOOKUPVALUE('Table1'[Job],'Table1'[Employee],SELECTEDVALUE('Table1'[Employee]))))

However, this does not work. 

What does work however, is

Peer Group Logged Hours = CALCULATE(

SUM('Table2'[Hours]),

FILTER('Table2','Table2'[Job]=LOOKUPVALUE('Table1'[Job],'Table1'[Employee],"Jessie")))

What am i doing wrong

[v-xiaotang]

Hi @CarlsBerg999 

Try this.

Measure =
  var _job=SELECTEDVALUE(Table1[Job])
return
  CALCULATE(SUM(Table2[Hours]),FILTER(ALL(Table2),Table2[Job]=_job))

Result:

Or if you want to modify the original measure, you can use function ALL().

Peer Group Logged Hours = CALCULATE(
SUM('Table2'[Hours]),
FILTER(ALL('Table2'),'Table2'[Job]=LOOKUPVALUE('Table1'[Job],'Table1'[Employee Name],"Jessie")))

Result:

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @CarlsBerg999 ,

I am not sure how would you like to display the results.

But you can try with below measure,

Measure = CALCULATE(SUM(Table2[Hours]),FILTER(Table2,Table2[Job]="Analyst"&&Table2[Employee Name]="Jessie"))