cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MichaelHutchens
Helper III
Helper III

CALCULATE measure not working with FILTER and COUNTA and USERELATIONSHIP

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

NameLocation
PeterAuckland

 

Table name = knowledge feedback_DUP

Rated UsefulName
YesPeter
NoPeter
YesSarah

 

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 🙂

1 ACCEPTED SOLUTION
AllisonKennedy
Super User
Super User

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


Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

4 REPLIES 4
omd001
Frequent Visitor

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?
 
COUNTUSERELATIONSHIP.PNG

@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


Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

AllisonKennedy
Super User
Super User

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


Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors