cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
janitor048
Helper I
Helper I

Distinct count in related table

Suppose I have the following tables

Releases

ReleaseID

A

B
C

 

Tickets

TicketIDReleaseIDStuff
1Axyz1
1Aabc1
2Bxyz2
2Babc2
3Bxyz3
4Cxyz4

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

B2
C1

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!

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

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


 

View solution in original post

3 REPLIES 3
Mariusz
Community Champion
Community Champion

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
Regular Visitor

@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.

 

@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!

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!