Forum Discussion
ddoshi
5 years agoNew Member
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 | ||||
| Project | Date | Staff | Cost | Ref (not actually in the table) |
| A | 1-Jan | John | 100 | z |
| A | 1-Jan | Peter | 200 | z |
| A | 1-Jan | 100 | z | |
| A | 1-Jan | 100 | z | |
| A | 1-Mar | Peter | 100 | y |
| A | 1-Mar | 100 | y | |
| A | 1-Mar | 100 | y | |
| A | 1-Mar | 100 | y | |
| A | 1-Mar | 100 | y | |
| B | 1-Mar | John | 10000 | x |
| Staff Table | ||||
| John | ||||
| Peter | ||||
| Output | ||||
| Staff | Measure Output | |||
| John | 10500 | Sum of z + x | ||
| Peter | 1000 | Sum of z + y |
Thanks in advance!
pls see the attachment below
1 Reply
- ryan_mayuSuper User