Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Join Tables and retrieve table

Hello Folks,   AFAIK, we cannot Join tables using calculated columns. But, I am now in a tricky situation that,   I have TableA and TableB.  TableA has calculated columns( NOT custom columns.). ...
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi Anonymous 
You create a new calculated table using this code

ExpectedOutput = 
SUMMARIZE (  
    CROSSJOIN ( 
        SELECTCOLUMNS ( TableB, "ID", TableB[GroupID], "Detail", TableB[GroupDetail] ), 
        TableA 
    ), 
    [PostingYear], 
    [ID],
    [Detail],
    "Spend", SUM ( TableA[ApprovedSpend] ) + 0
)

Anonymous's avatar
Anonymous
Not applicable
4 years ago

tamerj1 

Is it possible to avoid CROSSJOIN ? When in actual scenario, I have around 600 rows in TableA and around 100 rows in TableB. Applying crossjoin would take forever.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous 
    We are selecting the required columns only. Did you try?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      My bad, Tried. it works good. But, I am not able to get the Descriptions from TableB. It gives from TableA

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous 
    Just tried with 600x100 rows. it takes no time! unless you mean 600k x 100k?