Distinct count in related table

[janitor048]

Suppose I have the following tables

Releases

ReleaseID

A

B

C

Tickets

TicketID	ReleaseID	Stuff
1	A	xyz1
1	A	abc1
2	B	xyz2
2	B	abc2
3	B	xyz3
4	C	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.

What I want is a calculated column in Releases that counts the unique occurences of TicketID in table Tickets associated with this ReleaseID. So the result should be

ReleaseID	RelatedTickets
A	1
B	2
C	1

I can probably do this with explicit DISTINCCOUNT, CALCULATE and FILTER combination. However I would like to make use of the relation of the tables. But I can't get my head around this. I found the DISTINCT COUNT pattern https://www.daxpatterns.com/distinct-count/  however this looks at the RELATED side of the relation, here I would be requiring the RELATEDTABLE side (I guess).

Thanks for any hints!

[Mariusz · Accepted solution]

Hi @janitor048 

You can add a column like 

Column = CALCULATE( DISTINCTCOUNT( Tickets[TicketID] ) )

Or simply use a Measure 

Measure = DISTINCTCOUNT( Tickets[TicketID] ) 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[eliPR]

@Mariusz,

Just found this. Was having the same problem as the Janitor. The thing that I don't understand is why I don't have to use the RELATEDTABLE function. I thought that calculated columns disabled filter context. Why in this case does the calculated column apply the filter from the One table to the Many table?

Many thanks.

[janitor048]

@Mariusz that was kind of easy. Don't know why I haven't stumbled across this solution myself. Probably I was too busy trying to somehow get RELATEDTABLE in there...

Thanks!