cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
xjiang Frequent Visitor
Frequent Visitor

Issue with using information from both tables after creating relationship

Hello all,

 

I need some help for a project that I have been working on,

 

I have 2 tables (A: 40 columns with the planning stats) (B: 48 columns with the actual stats). Each table has more than 1 millon rows.

 

I used 7 columns that exist in both tables and created a unique index for each row. I then successfully created a relationship between these two tables: cardinality: 1:1, cross filter direction: both.

 

However, I started to have issues while I use those tables together. 

 

Here is an example:

I used origination and destination from table A, and want to use the average of minutes from table B, but Power BI won't show the results. When I added this column from table B, the origination and destination from table A just disappeared from the visual.

2018-10-04_13-31-09.jpg

 

In table B, origination and destination is named as Org and Des, will that be the problem? Both columns in both tables are in text format. The issue is not only with these two columns. even date from table A won't have the correct corresponding information from table B.

Can someone help me on this issue? Thanks a lot!!

1 ACCEPTED SOLUTION

Accepted Solutions
dedelman_clng New Contributor
New Contributor

Re: Issue with using information from both tables after creating relationship

Hi @xjiang

 

In Query Editor when you do "Merge Queries", you can match up the 7 columns that align between the two tables and use a "Left Outer" join to get all of the rows and columns from the Planning table, and all of the columns from the Actuals table that match up with a row in Planning, like so:

 

Capture.PNG

 

Hope this helps

David

6 REPLIES 6
dedelman_clng New Contributor
New Contributor

Re: Issue with using information from both tables after creating relationship

Did you try merging them together in PowerQuery (Query Editor) instead of in the model? Then you would only have one table to deal with.

xjiang Frequent Visitor
Frequent Visitor

Re: Issue with using information from both tables after creating relationship

Hi,

 

Thanks for your response!

 

Both of my tables are from SQL server and I don't have permission to create an extra index column in SQL. There's no single column that is unique enough for me to merge them together.

 

Is it possible to create an extra index column using the combination of existing columns in power bi query editor?

dedelman_clng New Contributor
New Contributor

Re: Issue with using information from both tables after creating relationship

Assuming you are doing Import (as opposed to DirectQuery - if you are using that others will need to help as I'm not familiar with it), in the Query Editor you go to Add Column -> Index Column -> From 1.  If you sort the tables in the same way prior to building the index the indexes should line up.

 

Capture.PNG

 

Hope this helps

David

xjiang Frequent Visitor
Frequent Visitor

Re: Issue with using information from both tables after creating relationship

Hi David,

 

Thanks again!

 

Will this solution work if the 2 tables don't have the same number of rows? The actual stats table didn't included the plans in table A that got cancelled.

 

Thanks,

Community Support Team
Community Support Team

Re: Issue with using information from both tables after creating relationship

Hi @xjiang,

 

Please try this measure, add it to table visual with columns origination and destination from table A.

average of minutes =
CALCULATE (
    AVERAGE ( TableB[Minute] ),
    USERELATIONSHIP ( TableA[RelatedColumn], TableB[RelatedColumn] )
)


Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dedelman_clng New Contributor
New Contributor

Re: Issue with using information from both tables after creating relationship

Hi @xjiang

 

In Query Editor when you do "Merge Queries", you can match up the 7 columns that align between the two tables and use a "Left Outer" join to get all of the rows and columns from the Planning table, and all of the columns from the Actuals table that match up with a row in Planning, like so:

 

Capture.PNG

 

Hope this helps

David