Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MichaelHutchens
Helper IV
Helper IV

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.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

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.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

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.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.