Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I want to use DAX to UNION 3 tables together. My issue is that 2 of the tables contains split columns...
Table1
ColumnA ColumnB ColumnC
Table2
ColumnA ColumnB
Table3
ColumnC
So normally, I could use
UNION(
SELECTCOLUMNS(Table1, "Title", ColumnA, "Name", ColumnB),
SELECTCOLUMNS(Table2, "Title", ColumnA, "Name", ColumnB)
)
But how can I incorporate ColumnC from Table1 and from Table3?
I'd like to use DAX and not have to merge via Power Query.
Hi @Anonymous
please try
=
UNION (
UNION (
SELECTCOLUMNS (
Table1,
"Title", Table1[ColumnA],
"Name", Table1[ColumnB],
"Value", Table1[Columnc]
),
SELECTCOLUMNS (
Table2,
"Title", Table2[ColumnA],
"Name", Table2[ColumnB],
"Value", BLANK ()
)
),
SELECTCOLUMNS (
Table3,
"Title", BLANK (),
"Name", BLANK (),
"Value", Table3[Columnc]
)
)
for some reason, this produced blank rows for Table3[ColumnC] values that match to Table2[ColumnA] and Table2[ColumnB] - the values for Table3[ColumnC] did not pull in at all.
@Anonymous
Yes it happens sometimes. First adjust the code as follows (make sure the order of the columns in the code is correct)
=
UNION (
SELECTCOLUMNS (
Table1,
"Title", Table1[ColumnA],
"Name", Table1[ColumnB],
"Value", Table1[Columnc]
),
SELECTCOLUMNS (
Table2,
"Title", Table2[ColumnA],
"Name", Table2[ColumnB],
"Value", BLANK ()
),
SELECTCOLUMNS (
Table3,
"Title", BLANK (),
"Name", BLANK (),
"Value", Table3[Columnc]
)
)
If this didn't work, copy the code and create a new table then delete the old one.
User | Count |
---|---|
20 | |
18 | |
17 | |
11 | |
7 |
User | Count |
---|---|
28 | |
28 | |
13 | |
12 | |
12 |