Forum Discussion
How do I retrieve values from non-related table | Lookupvalue help?
I am creating a project management table that shows project forecasted hours (planned hours), project actual hours worked, and a column for the difference. The forecast hours and actual hours tables have no relationship and show many-to-many when trying to connect them. Using the forecast table, I've tried pulling the actual hours over using functions like lookupvalue, but I either get errors or the actual hours values in the table I make are incorrect. I tried searching the forums some, but when trying some of the things in the comments, I couldn't get them to work (such as lookupvalue, merged queries) whether that's me not understanding or the data being too different for me to grasp how the dax could work in my tables.
Wanted result:
| Worker | Project | Forecasted Hours | Actual Hours | Difference |
| Andy Man | 001 Project | 2 | 1 | 1 |
| Andy Man | 002 Project | 2 | 2 | 0 |
| Bob Bob | 001 Project | 3 | 1 | 2 |
| Charlie Man | 001 Project | 5 | 3 | 2 |
Below is my pbix file with mockup data that is close to my actual data. I've created the relationships and in the forecast hours table I left in the column where I tried lookup value and failed.
Link to my pbix file
Thank you in advance for any help with this.
Anonymous - You have two fact tables. Yes they are not related directly to each other, but there can be using Bridging Dimension like "Worker" and "Calendar". There table will apply filter context to the two fact tables. You need to create tables for each other dimension - role, project. Then just find the Actual and Plan columns, and create a measure table.
1 Reply
Anonymous - You have two fact tables. Yes they are not related directly to each other, but there can be using Bridging Dimension like "Worker" and "Calendar". There table will apply filter context to the two fact tables. You need to create tables for each other dimension - role, project. Then just find the Actual and Plan columns, and create a measure table.