Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone,
I'm trying to create a measure that will count the number of team members under each manager. I already have a many to one relationship between the employees data and the manager names' table.
Please help! It seems simple but I can't figure out what's wrong with my model.
This is the measure I have:
NumofTeamMembers= CALCULATE(COUNTA('Employee'[Name]),'Employee' [ManagerName]='Managers'[ManagerName])
The error message is the following: The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.
Any help is really appreciated, thank you!!
Solved! Go to Solution.
@Anonymous Try just a simple measure:
NumofTeamMembers= COUNTA('Employee'[Name])
Then put this in a visual in Values with Manager table, Manager column in the columns/rows/axis field (depending on what visual you select).
Also, please set the cross filter direction of your relationship to single if you don't need it to both. https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_92.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
@Anonymous You're welcome. 🙂
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
Thank you, the function works!!
@Anonymous Try just a simple measure:
NumofTeamMembers= COUNTA('Employee'[Name])
Then put this in a visual in Values with Manager table, Manager column in the columns/rows/axis field (depending on what visual you select).
Also, please set the cross filter direction of your relationship to single if you don't need it to both. https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power_92.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
@Anonymous , I do not see need ot this join
NumofTeamMembers= CALCULATE(COUNTA('Employee'[Name]))
if needed it will work like
NumofTeamMembers= CALCULATE(COUNTA('Employee'[Name]),filter('Employee', 'Employee' [ManagerName]=max('Managers'[ManagerName])))
if you need Avg
averagex(Values('Managers'[ManagerName]) , CALCULATE(COUNTA('Employee'[Name])))
Thank you, it works!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |