Forum Discussion

xjiang's avatar
xjiang
Frequent Visitor
7 years ago
Solved

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.

 

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

Need Help
  • dedelman_clng's avatar
    dedelman_clng
    7 years ago

    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

6 Replies

  • dedelman_clng's avatar
    dedelman_clng
    Community Champion
    7 years ago

    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's avatar
      xjiang
      Frequent Visitor
      7 years ago

      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's avatar
        dedelman_clng
        Community Champion
        7 years ago

        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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    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