Select distinct rows for SUMX for related table

[janitor048]

I have two tables.

Releases

ReleaseID

A

B

C

Tickets

TicketID	ReleaseID	MyValue	Stuff
1	A	2	xyz1
1	A	2	abc1
2	B	5	xyz2
2	B	5	abc2
3	B	1	xyz3
4	C	10	xyz4

Both tables are related in the data modell via the ReleaseID fields, i.e. the relation is 1:m from table Releases to table Tickets. I am looking for a way to get the sum of MyValue per ReleaseID for distinct values of TicketID. The value of MyValue is guaranteed to be identical for all rows with the same TicketID, those rows only differ by the entries in Stuff. But I want to count each TicketID only once in my sum. 

So the result should be:

ReleaseID	SumMyValue
A	2
B	6
C	10

I guess I would somehow have to combine a SUMX over the RELATEDTABLE plus filtering on distinct values. But I can't get it to work.

Right now all my attempts lead to summing all MyValue values for the rows in Tickets, thus yielding A -> 4, B -> 11, C -> 10.

[parry2k · Accepted solution]

@janitor048 try this measure, Table 2  in my measure is table from many side and Table  is from One side

Total Value = SUMX ( SUMMARIZE ( 'Table 2', 'Table'[ReleaseID], 'Table 2'[TicketID] ), CALCULATE ( MAX( 'Table 2'[MyValue] ) ) )

Would appreciate Kudos 🙂 if my solution helped.

[janitor048]

@parry2k thanks for your suggestion.

It works - but I can't yet really figure out why and how. Putting your formula in a measure and then creating a calculated column that uses this measure gives the correct results per row in my "one-side" table.

Putting your formula directly into the calculated column however gives incorrect results.

Could you maybe elaborate a bit more on how SUMX does it's magic here?

[parry2k]

@janitor048 summarize is making the unique row and taking the maximum value of it and then sumx is summing it up. Hope this helps.