Hi,
I've been using Power Query for some time, just picking up Power Pivot. I have two tables- headcount & transactions (hires, exits, promotions, etc). with employee ID in common. I am able to do a RELATED expression in the headcount table, and show the transactions for the employees in that list. But the expression does not work the other way- I cannot lookup band, or location, or any other details in the headcount report, from the transactions report. Simply put, the relationship works one way and not the other.
The error message I get is that:
The column 'HC[Employee Work Country Code]' either doesn't exist or doesn't have a relationship to any table available in the current context.
This also affects the pivot table, as Power Pivot fails to detect a relationship between the two tables. What am I missing here?
Thanks!
Sree
Hi Sreedevi,
Have you completed the Power Pivot course? I ask because your scenario sounds like you either don't have a dimension (dim) table for the employee details. It sounds like both the Headcount and Transactions tables might both be Fact tables.
Or, the relationship is going in the wrong direction.
Please watch the lessons in session 6 of the Power Pivot course and let me know if you're still stuck after that. If you are, please provide screenshots or sample data of your tables and diagram view. You can desensitise as required.
Thanks,
Mynda
Thanks Mynda - I think that might be the problem, both are Facts tables. My temporary fix is to add a new column in HC and use "Related" to pull data from the transactions report, and then pivot. It won't work in the long run when I need the details from headcount (band, country) to analyze the transactions. Thanks anyway! Let me see if I can find a solution in the last bits of the course.
