How get distinct values from a table and use them to get data from related table?

Anonymous — Thu, 27 Aug 2020 08:59:42 GMT

I have a couple tables. FactTable1 is connected to all dimensions and FactTable2 has some preagregated data and has only one realtion to FactTable1 (many) <-FactTable2(one) using CompositeId.
Tables structure:

FactTable1

PrimaryId

CompositeId

....

Amount

and

FactTable2

CompositeId

DifferentAmount

I would like to use FactTable1 distinct CompositeId values and get a sum of FactTable2[DifferentAmount].
My current code that doesn't work:

//Code works, but return bigger result that expected //probably, because the calculation has duplications and adds the same value //from FactTable2[DifferentAmount] several times SUMX ( FactTable1, RELATED (FactTable2[DifferentAmount]) ) //This code should remove duplicates, but it doesn't work SUMX ( DISTINCT (FactTable1[CompositeId]), RELATED (FactTable2[DifferentAmount]) )

Re: How get distinct values from a table and use them to get data from related table?

amitchandak — Thu, 27 Aug 2020 10:22:45 GMT

Anonymous , try like

SUMX (FactTable1,
maxx(values(FactTable1[CompositeId]),
RELATED (FactTable2[DifferentAmount]))
)

topic Re: How get distinct values from a table and use them to get data from related table? in DAX Commands and Tips

How get distinct values from a table and use them to get data from related table?

Re: How get distinct values from a table and use them to get data from related table?