• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Relationships in Data Model

A

aggie81

Member
I would like to use a Pivot Table based on Rows with fields named
Update
Description
# of Crops
Sell Date
Area & Tables

Values based on
Finish Units
Units Planted
Units Booked (from query SPRBKNG18)

Filters
Cust Name (from query SPRBKNG18)
Pot Size
Group

I thought I could do it using relationship in Data Model. I created a primary field in tblSPR18 with no duplicates in UniqueID and a child field in SPRBKNG18 but with duplicates. It should be a one to many relationship.
I use the spreadsheet to plan horticultural crops and keep track of the orders
Think of it like an invoice with one item but many purchasers.
The problem is that it doesn't know what to do when I try to use the fields from SPRBKNG18 in the Pivot table. It asks me to create a relationship and I don't know where to go with the problem.
The file is too large to upload and I hope you can look at it with the link below.

https://jjlgreenhouseinc-my.sharepo...ZBsEwSog7SWHcBL-PuluosbcpxAuvzYOaGHQ?e=jZ8H4c

Thank you for looking and any help is appreciated.

Lee
 
Hi Lee,

I first thought you created the relationship in the wrong way. But it does not change anything.
Then I noticed your data is not normalized. The real link is your item number, but in non of the tables it is a unique key. Both tables appear as transaction tables. It is a best practice to have a transaction table linked to lookup tables. Sales linked to a unique customer table and a unique product table for example.
I notice you do use Power Query as well. Why don't you merge both tables over there? PQ knows join types (try a full outer on item number). Since you are not using explicit dax-measures anyway, the point of having it all in PP is close to non-added value in this case.
And you can create the "pivots" also in PQ.
 
Last edited:
The Full Outer on Item Number worked very well! I wouldn't have ever thought of doing it that way.
I always learn so much from the group here.
Thank you for taking the time to respond and again the answer was what I was looking for.
Lee
 
I can't open the attachments. It gives an error message that the attachment can't be found. I am logged into the website.
I appreciate your help.
Thanks
 
You must log in or register to reply here.
Back
Top