Forum Discussion
Count based on values in 2 other table
I've tried searching this topic, but I must not know the right terms to search because I can't find a solution. So apologies if this is a repeat topic. Currenlty I'm trying to figure out how to count values based on strings in 2 other tables.
I have 3 tables: User, Case, and Comments. Structured as follows:
User
Id
Name
Case
CreatedByID
Id
Origin
Comment
Id
ParentId
CreatedById
The tables are related to each through the following relationships: User.Id>Case.CreatedById, Case.Id>Comment.ParentId
What I'm trying to do is count the number of comments made on a case with a specific origin made by a specific user. Another way of saying it would be to count the comments WHERE Case.Origin="X" and then use a slicer on User.Name to specify the user that made them. So far I've been trying to use COUNTAX(FILTER('Comment', RELATED('Case'[Origin]="X" but that doesn't seem to be filtering properly.
I hope this made sense and thanks for any help.
3 Replies
Hey mthompson ,
Please create a pbix file containing sample data that still reflects your data model (tables, relationships, calculated columns, and measures). Upload the pbix to onedrive, google drive, or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method, share the Excel as well.
Describe the expected result based on the sample data you provide.
Regards,
Tom
So I built some sample data and dashboard and my DAX works prefectly so it appears to be somthing with my relationships/modeling rather than the actual DAX.
Comments Count = COUNTAX(FILTER('Comment', RELATED('Case'[Origin])="Phone"), [Id]) did in fact count exactly like I wanted.I'm going to call this resolved as it's not the DAX that's giving me issues, I'll play around with the model and see where I went wrong.Just a few suggestions for your DAX that also might help out. For one, try wrapping your entire DAX function within a CALCULATE function. This will help in providing context to DAX on how the measure should be calculated. Then, based on your relationships you might not even need the RELATED function especially if you wrap the measure in CALCULATE. The measure could technically be re-written as:
Comments Count =CALCULATE(COUNT('Comment'[Id]),FILTER('Case','Case'[Origin] = "Phone"))
