Reply
Member
Posts: 76
Registered: ‎08-29-2016
Accepted Solution

Relationship not seen when trying to create new column

[ Edited ]

Hi,

 

I am trying to troubleshoot an issue with a relationship.

 

I have two tables, one with PART #s and a costing Plan, and another with PART #s and actual sales data. I would like to link the two based off of the PART#. I am trying to create a new column in the sales data table, and reach out and grab information for those parts in the costing plan, but it won't let me.

 

A couple bits of info that may be screwing it up:

* Each sheet has parts that the other doesn't in it

* When trying to manage relationships, it will only allow me many to one (cardinality) from costing plan (many) to sales data (one).

 

Thank you for any help in advance.


Accepted Solutions
New Contributor
Posts: 548
Registered: ‎08-03-2016

Re: Relationship not seen when trying to create new column

Assuming you do have a join line, then in your formula, presuming your starting point record set is Table 1, you use RELATED ( ) with the field in Table 2.  You can look up that function/method.  Once you get that all should work.

View solution in original post


All Replies
Highlighted
New Contributor
Posts: 544
Registered: ‎10-27-2015

Re: Relationship not seen when trying to create new column

What do you need to bring into the Sales table?

 

Since Costing Plan is on the many side, there will be multiple rows per row in Sales, so you'll need to summarize or aggregate the data from Costing Plan in some way. How would you like to do that?

Member
Posts: 76
Registered: ‎08-29-2016

Re: Relationship not seen when trying to create new column

There are costing elements that I would like to use in a formula in the sales table in a new column. These tables are unique and have no repeated values for PART #s, so I don't understand why I am only allowed to have a one to many relationship, with the costing plan being the many.

New Contributor
Posts: 548
Registered: ‎08-03-2016

Re: Relationship not seen when trying to create new column

[ Edited ]

Based on what I read I would recommend you make a new Master Part table.  By this I mean a table that has every part #.  Make this part of your data model.  Or append the unique Part #s of the Sales table to the Cost table and consider the Cost table to be the master.

 

Everything down the road will be much more straightforward for you if conceptually a master table exists.

 

Also be sure you have downloaded the latest build of Power BI this month.

 

 

Member
Posts: 76
Registered: ‎08-29-2016

Re: Relationship not seen when trying to create new column

[ Edited ]

When trying to append the Sales table to the cost table, I got this. Capture.PNG

I guess I am a bit confused on the best way to either create a master  in BI or append.

New Contributor
Posts: 548
Registered: ‎08-03-2016

Re: Relationship not seen when trying to create new column

My advice was conceptual.  The implementation is tricky within PBI - and one should consider whether you have a source outside your current PBIX file that is the master table already and just import that - which is easier than manipulating inside PBI.  Also one must consider if the data is ever to be refreshed as to how one deals with this.

 

Having said that - if the Cost table is to be the master; you want just the parts (Part #) of the Sales table that are unique (don't exist in the Part Table).  Then append just those numbers (1 column) into the Cost table.  Since you don't have costs for these parts you wouldn't have any other data in the Cost table for them - just the part #.  You definitely do not want any duplicating part #s in the Cost table (or in the master table if you make a separate master table instead).

 

This was all started by your comment that the 2 tables had some unique Parts in each - and so that immediately struck me as something I would resolve upfront by establishing a master table as part of the data model set up.  I would tend to go to the source and get it from outside PBI.  But using the DISTINCT and VALUES functions along with Create New Table and Append features it probably can be done inside PBI.  Don't mean to send you on a wild goose chase if those parts are miscellaneous or not important to your data set - but otherwise setting up the correct data model at the start is a good thing to do.

 

Sorry for the longish and vague reply......

 

 

 

 

Member
Posts: 76
Registered: ‎08-29-2016

Re: Relationship not seen when trying to create new column

No worries, I am new  to the software and learning as I go. It will be a monthly update, and the updated (sales) data may have new unique part numbers each month. Which is why it would be nice to somehow get BI to automate this  process for me instead of making a master list in the external source. Does this make sense?

New Contributor
Posts: 548
Registered: ‎08-03-2016

Re: Relationship not seen when trying to create new column

makes sense. So assuming you have the latest PBI installed.  If it is detecting repeating part # in Cost table but not in Sales table - then that is why it is thinking the Many:1 is the way it is. 

 

for most of us it is usually 1 Cost for a part and many Sales - but that is an assumption that may not be valid in your case

 

you would join on the Part # field assuming you are going to relate a cost to a sale....

 

in your calculations you can invoke an outer join so that you get all records of 1 table; 

 

in a new project I myself would want to establish either a master Part table stand alone or designate one of the tables to become the master (assuming it never will repeat a Part #).... just to set up the data model baseline - but that's just me.....

 

 

 

 

Member
Posts: 76
Registered: ‎08-29-2016

Re: Relationship not seen when trying to create new column

Okay, I put together a master list of parts with no duplicates and have a working relationships. I am looking to create a column and pull costing information against sales information. When typing the formula, I am not allowed to access the opposite table from what I am working in. Why could this be?

New Contributor
Posts: 548
Registered: ‎08-03-2016

Re: Relationship not seen when trying to create new column

Assuming you do have a join line, then in your formula, presuming your starting point record set is Table 1, you use RELATED ( ) with the field in Table 2.  You can look up that function/method.  Once you get that all should work.