CALCULATE measure not working with FILTER and COUNTA and USERELATIONSHIP

[MichaelHutchens]

Hi everyone, I'm hoping someone can help 🙂

I have two tables, joined with an inactive relationship based on a user's name which exists in both tables. I'm attempting to count the 'Yes' strings in the 'Rated Useful' column of one table, with a filter based on the user's name in a column in the other table. I'm using the below measure in a table called 'knowledge feedback_DUP', however the USERELATIONSHIP part doesn't seem to be working:

DUP_INACTIVE_Count of Ratings =
CALCULATE (
COUNTA('knowledge feedback_DUP'[Rated Useful]),
USERELATIONSHIP ('knowledge feedback_DUP'[Feedback User], 'users'[name] ),
FILTER('knowledge feedback_DUP','knowledge feedback_DUP'[Rated Useful] = "Yes")
)

Table name = users

Name	Location
Peter	Auckland

Table name = knowledge feedback_DUP

Rated Useful	Name
Yes	Peter
No	Peter
Yes	Sarah

Could anyone see anything obviously wrong with what I'm doing here, e.g. can you use a FILTER and a USERELATIONSHIP with a COUNTA, all inside a CALCULATE function? I've verified my data and I should be getting some numbers back:

• I'm getting blank results when I attempt to create a table visual with 'users'[Name] and 'knowledge feedback_DUP'[DUP_INACTIVE_Count of Ratings]
• I'm getting the correct results when I create a table visual with 'knowledge feedback_DUP'[Name] and 'knowledge feedback_DUP'[DUP_INACTIVE_Count of Ratings]

Thanks so much 🙂

[omd001]

Hi @AllisonKennedy 
I have similar issues where I would like to count the number of projects with status "Completed" based on an inactive date relationship "Actual Completion Date".

I use the following command:

Completed =

CALCULATE(

COUNTROWS(FILTER('FACT Project','FACT Project'[Status] = "Completed")

),

USERELATIONSHIP('DIM Dates'[Date],'FACT Project'[Actual Completion Date]))+0

It's returning me 0 instead of 14 as a count. Would you be able to help?

[AllisonKennedy]

@omd001  I'm not sure what your screenshot is showing - the left side is cut off and if you could share what's in the visualizations pane that would also be helpful. Verify you're using the 'DIM Dates' table for the month filter and also that the date format matches in both tables. I recommend using a DateKey for the relationship rather than date, but not sure if that's the issue you're having without more info. https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

[AllisonKennedy · Accepted solution]

@MichaelHutchens You can do your filter in the Count function:

DUP_INACTIVE_Count of Ratings =
CALCULATE (
COUNTROWS(FILTER('knowledge feedback_DUP','knowledge feedback_DUP'[Rated Useful] = "Yes")
),
USERELATIONSHIP ('knowledge feedback_DUP'[Feedback User], 'users'[name] )

I'm assuming you've done this as a calc column in the Users table?

[MichaelHutchens]

Perfect, thanks so much for the quick reply @AllisonKennedy 🙂 Much appreciated 🙂