August 8, 2021
I'm new to Power Query and Power Pivot. I'm creating a dashboard, and having some trouble mastering the relationships between tables. There's some constraints / database logic at work.
In this simplified example I'd like to create a pivot table that shows the "Client Rate" applicable to the "Named Resource"
e.g. Amy, is an actor. Actors are classified as Entertainment. Entertainment has a client rate of $150. Therefore I'd like to show in a pivot table Amy, and the rate of $150. Refer to the image attached
Named Resource, Role A
Cristina, Bar person
Role B, Classification
Bar person, Hospitality
Classification, Client Rate
The tables are joined 1 to many on Role (Tables A to B), and Classification (Tables B to C).
When I create a pivot table with
- Named Resource & Role A in rows, and
- Sum of Client rate in Values,
Hope that makes sense. Can you help me achieve the above result, and understand the database logic that makes it work the way it does?
July 16, 2010
December 7, 2016
I understand what you are trying to achieve, but even though Excel can handle big data in the Data Model it is not a relational database as such.
Relationships in the Data Model works when you have a 1 to many relation, where the tables on the many side are the tables where you want to get the values from and where the tables on the 1 side are those which you want to sort or filter by.
Based on your sample data (which in reality is to small to be separated like this) you should have following tables.
Named resource, Role, Internal rate, Client rate
August 8, 2021
Thanks Mynda and Anders,
I was assuming because the 3 tables were linked that I could then just data from table 1 to get related data from table 3. From my experimenting and your answer Anders I can see that this is not the way forward.
Instead I used the Join function. A left join gave me the connections I needed to show the person, and their rate, and to also keep the data tables in the same structure.