Forum Discussion
Sum Distinct Values from different tables in DAX
Hello, I am trying to sum distinct values from 2 tables. Table 1 containts the Part numbers shipped from location 1. Table 2 containts the Part numbers shipped from location 2. I would l...
LivioLanzo
Solution Sage
Solution SageHI soldanr
the simplest way to do this is to combine table1 and tabl2, adding an extra column to identify the location.
afterwards, you can summarize this easily with a matrix
if you wanted to do it with a DAX query to create a new table, you can do it like this:
=
GROUPBY (
UNION (
ADDCOLUMNS ( Ship1, "Location", "Location 1" ),
ADDCOLUMNS (
SELECTCOLUMNS ( Ship2, "PN", [PN], "QTYSHP1", [QTYSHP2] ),
"Location", "Location 2"
)
),
Ship1[PN],
"TOT", SUMX ( CURRENTGROUP (), [QTYSHP1] )
)
Unfortunately I am using direct query and I don't have the option to create a new table.
I like the union solution, it is very elegant but I can't make it work :(