Forum Discussion

ddoshi's avatar
ddoshi
New Member
5 years ago
Solved

Trouble with filter context

I have a question on the best way to achieve the following. I have a fact table, staff table and output as presented in the tables below. I want to create a measure that for each selected staff (calculated using selectedvalue), will add all the costs associated with projects they worked on, on the dates they worked. I can't figure out how to structure the filter in my calculate statement given that rows I want to include in my sum may not have a staff name associated with it.

 

Fact Table    
ProjectDateStaffCostRef (not actually in the table)
A1-JanJohn100z
A1-JanPeter200z
A1-Jan 100z
A1-Jan 100z
A1-MarPeter100y
A1-Mar 100y
A1-Mar 100y
A1-Mar 100y
A1-Mar 100y
B1-MarJohn10000x
     
     
     
Staff Table    
John    
Peter    
     
Output    
StaffMeasure Output   
John10500Sum of z + x 
Peter1000Sum of z + y 

 

Thanks in advance!