Forum Discussion
Distinct count in related table
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!
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
3 Replies
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
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!
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.
