Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Thanks so much 🙂
Solved! Go to 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?
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
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:
@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
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
@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?
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 🙂
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |