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

[HamidBee]

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. 

[v-janeyg-msft · Accepted solution]

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

[AlexisOlson]

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?

[bcdobbs]

I think you want UNION instead of calculatetable.

[HamidBee]

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