Search code examples
powerbidaxstudio

PowerBI: Create a new table using two columns from two different tables

I'm struggling a little but with this one. What I'm trying to is to create a new table using two columns of ID's from two different tables which I can then compare for duplicate values, highlight them and show those that are not dups.

I used the following DAX to create a new table

UNION (
    SELECTCOLUMNS ( FAACD, "Column1", [best_assignment] ),
    SELECTCOLUMNS ( FAACD_All, "Column2", [doc_id] )
)

But the union command is just stacking up everything all under the one column. How can i get the two columns to show separately?

I tried to use SUMMARIZE

New Table =
UNION (
    SUMMARIZE ( Table1, [Group_by_Me], "Column1", [Column1] ),
    SUMMARIZE ( Table2, [Group_by_Me], "Column2", [Column2] )
)

this put things in separate columns but i didn't give me the result i wanted, i just want two unfiltered columns in a new table

Solution

  • How about using

    UNION (
    SELECTCOLUMNS ( FAACD, "Column1", [best_assignment], "Column2", BLANK() ),
    SELECTCOLUMNS ( FAACD_All, "Column1", BLANK(), "Column2", [doc_id] )
)