Forum Discussion

CarlsBerg999's avatar
CarlsBerg999
Icon for Helper V rankHelper V
5 years ago

Using slicer to select a related value

Hi,

 

I have a table with Employee names and Jobs:

 

Employee NameJob
ToddAnalyst
FrankController
JessieAnalyst

 

Another table has Employee Names and total hours:

 

Employee NameJobHours
ToddAnalyst4
ToddAnalyst6
FrankController4
FrankController9
JessieAnalyst3

 

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

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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"))
  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    5 years ago

    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.