Forum Discussion

HamidBee's avatar
HamidBee
Power Participant
4 years ago
Solved

Problem Creating a table using columns from different tables (DAX)

I am using the following code to join 4 columns from two different tables:

 

CALCULATETABLE(

    SELECTCOLUMNS('2020',"values",'2020'[Value],"Dates",'2020'[Dates]), 

     SELECTCOLUMNS('2021',"values",'2021'[Value],"Dates",'2021'[Dates])

     )

 

Strangely it only returns table '2020' columns. The '2021' columns do not appear. I think there may be something wrong with my code I just found it strange that Power BI did not detect any errors in the code.

 

Any help is appreciated. 

Need Help
Show and Tell
Tips and Tricks
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, HamidBee 

     

    Calculatetable is not used to connect tables, so the latter code has no effect.

    CALCULATETABLE function (DAX) - DAX | Microsoft Docs

    Calculatetable and Union function both will create a new table.

    Like this:

    Table =
UNION (
    SELECTCOLUMNS ( '2020', "values", '2020'[Value], "Dates", '2020'[Dates] ),
    SELECTCOLUMNS ( '2021', "values", '2021'[Value], "Dates", '2021'[Dates] )
)

    This table can be displayed in the desktop as a new table, or as a variable table in the measure.

    If you don't want to add the third table, you can easily append them in PowerQuery.

    Like this:

     

    Below is my sample.

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
     If not, please feel free to ask me.
     
    Best Regards,
    Community Support Team _ Janey

4 Replies

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    I think you want UNION instead of calculatetable.

    • HamidBee's avatar
      HamidBee
      Power Participant
      4 years ago

      The issue I'm having with UNION is that it appends the table. I couldn't find a work around for it. Any suggestions? Also is it not possible for CALCULATETABLES to be used to join two columns from differnet tables together?

       

      Thanks

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    CALCULATETABLE expects a table for the first argument and filters for any additional arguments, so the 2021 columns are being used as filters on the 2020 columns (which doesn't change anything if these tables are unrelated). This is valid DAX even if it isn't doing what you expect it to.

     

    From the names of the columns you've assigned, I would have assumed you wanted to union these (like bcdobbs assumed) but, apparently, you want something else. What is the result you're expecting instead?

     

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    4 years ago

    Hi, HamidBee 

     

    Calculatetable is not used to connect tables, so the latter code has no effect.

    CALCULATETABLE function (DAX) - DAX | Microsoft Docs

    Calculatetable and Union function both will create a new table.

    Like this:

    Table =
UNION (
    SELECTCOLUMNS ( '2020', "values", '2020'[Value], "Dates", '2020'[Dates] ),
    SELECTCOLUMNS ( '2021', "values", '2021'[Value], "Dates", '2021'[Dates] )
)

    This table can be displayed in the desktop as a new table, or as a variable table in the measure.

    If you don't want to add the third table, you can easily append them in PowerQuery.

    Like this:

     

    Below is my sample.

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
     If not, please feel free to ask me.
     
    Best Regards,
    Community Support Team _ Janey