Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have 7 tables that I would like to combine together into a master table using DAX. There are an uneven amount of colums between all the tables out of 5 possible columns. For the tables with missing columns, I have incorporated the column name using "column name", blank(). They all will have a datasource column, and then a combination of the other 4 columns.
Table 1 and Table 2: datasource and columns 1-4
Table 3: datasource and columns 1,3,4
Table 4 and 5: datasource and column 1
Table 6: datasource and columns 2-3
Table 7: datasource and columns 3-4
I have used the code below for the union of all the tables and then created a second Table to summarize columns 1-4 and then add a datasource column that concatenates the datasource columns together. This works great, but what I'm finding is those that have blank() for columns, could potentially be summarized, but are not being summarized because the blank value doesn't match what the other datasources have for those columns. For example, Table 4 and 5 only have the 1st column (computername) and nothing else. I'm basically trying to do in DAX like you would do with append, merge and Group by using Text.Combine in Power Query but I need to do this in DAX. I am open to any and all suggestions.
CombinedAssets =
Distinct(
UNION(
SELECTCOLUMNS(table1,
"Datasource", table1[Datasource],
"Column1", table1[computername],
"Column2", table1[computerid],
"Column3", table1[serialnumber],
"Column4", table1[macaddress]
), //closes table1
SELECTCOLUMNS(table2,
"Datasource", table2[Datasource],
"Column1", table2[computername],
"Column2", table2[computerid],
"Column3", table2[serialnumber],
"Column4", table2[macaddress]
), //closes table2
SELECTCOLUMNS(table3,
"Datasource", table3[Datasource],
"Column1", table3[computername],
"Column2", blank(),
"Column3", table3[serialnumber],
"Column4", table3[macaddress]
), //closes table3
SELECTCOLUMNS(table4,
"Datasource", table4[Datasource],
"Column1", table4[computername],
"Column2", blank(),
"Column3", blank(),
"Column4", blank()
), //closes table4
SELECTCOLUMNS(table5,
"Datasource", table5[Datasource],
"Column1", table5[computername],
"Column2", blank(),
"Column3", blank(),
"Column4", blank()
), //closes table5
SELECTCOLUMNS(table6,
"Datasource", table6[Datasource],
"Column1", blank(),
"Column2", table6[computerid],
"Column3", table6[serialnumber],
"Column4", blank
), //closes table6
SELECTCOLUMNS(table7,
"Datasource", table7[Datasource],
"Column1", blank(),
"Column2", table2[computerid],
"Column3", table2[serialnumber],
"Column4", table2[macaddress]
) //closes table7
)//closes union
) //closes distinct
Solved! Go to Solution.
Hi @Neiners ,
I think you can change your DAX codes.
CombinedAssets =
DISTINCT(
UNION(
SELECTCOLUMNS(
table1,
"Datasource", table1[Datasource],
"Column1", table1[computername],
"Column2", table1[computerid],
"Column3", table1[serialnumber],
"Column4", table1[macaddress]
),
SELECTCOLUMNS(
table2,
"Datasource", table2[Datasource],
"Column1", table2[computername],
"Column2", table2[computerid],
"Column3", table2[serialnumber],
"Column4", table2[macaddress]
),
SELECTCOLUMNS(
table3,
"Datasource", table3[Datasource],
"Column1", table3[computername],
"Column2", COALESCE(blank(), "N/A"),
"Column3", table3[serialnumber],
"Column4", table3[macaddress]
),
SELECTCOLUMNS(
table4,
"Datasource", table4[Datasource],
"Column1", table4[computername],
"Column2", COALESCE(blank(), "N/A"),
"Column3", COALESCE(blank(), "N/A"),
"Column4", COALESCE(blank(), "N/A")
),
SELECTCOLUMNS(
table5,
"Datasource", table5[Datasource],
"Column1", table5[computername],
"Column2", COALESCE(blank(), "N/A"),
"Column3", COALESCE(blank(), "N/A"),
"Column4", COALESCE(blank(), "N/A")
),
SELECTCOLUMNS(
table6,
"Datasource", table6[Datasource],
"Column1", COALESCE(blank(), "N/A"),
"Column2", table6[computerid],
"Column3", table6[serialnumber],
"Column4", COALESCE(blank(), "N/A")
),
SELECTCOLUMNS(
table7,
"Datasource", table7[Datasource],
"Column1", COALESCE(blank(), "N/A"),
"Column2", table7[computerid],
"Column3", table7[serialnumber],
"Column4", table7[macaddress]
)
)
)
You can use COALESCE function and "N/A" placeholder.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Neiners ,
I think you can change your DAX codes.
CombinedAssets =
DISTINCT(
UNION(
SELECTCOLUMNS(
table1,
"Datasource", table1[Datasource],
"Column1", table1[computername],
"Column2", table1[computerid],
"Column3", table1[serialnumber],
"Column4", table1[macaddress]
),
SELECTCOLUMNS(
table2,
"Datasource", table2[Datasource],
"Column1", table2[computername],
"Column2", table2[computerid],
"Column3", table2[serialnumber],
"Column4", table2[macaddress]
),
SELECTCOLUMNS(
table3,
"Datasource", table3[Datasource],
"Column1", table3[computername],
"Column2", COALESCE(blank(), "N/A"),
"Column3", table3[serialnumber],
"Column4", table3[macaddress]
),
SELECTCOLUMNS(
table4,
"Datasource", table4[Datasource],
"Column1", table4[computername],
"Column2", COALESCE(blank(), "N/A"),
"Column3", COALESCE(blank(), "N/A"),
"Column4", COALESCE(blank(), "N/A")
),
SELECTCOLUMNS(
table5,
"Datasource", table5[Datasource],
"Column1", table5[computername],
"Column2", COALESCE(blank(), "N/A"),
"Column3", COALESCE(blank(), "N/A"),
"Column4", COALESCE(blank(), "N/A")
),
SELECTCOLUMNS(
table6,
"Datasource", table6[Datasource],
"Column1", COALESCE(blank(), "N/A"),
"Column2", table6[computerid],
"Column3", table6[serialnumber],
"Column4", COALESCE(blank(), "N/A")
),
SELECTCOLUMNS(
table7,
"Datasource", table7[Datasource],
"Column1", COALESCE(blank(), "N/A"),
"Column2", table7[computerid],
"Column3", table7[serialnumber],
"Column4", table7[macaddress]
)
)
)
You can use COALESCE function and "N/A" placeholder.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
34 | |
14 | |
12 | |
12 | |
11 |