Issue with using information from both tables after creating relationship

[xjiang]

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.

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!!

[v-yulgu-msft]

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

[dedelman_clng]

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]

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]

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.

Hope this helps

David

[xjiang]

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,

[dedelman_clng · Accepted solution]

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:

Hope this helps

David