Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have an employee directory that lists every employee in the company, along with the office they are assigned to. I need to provide a measure that tells us what percentage of our offices have 10 or more employees, but this is proving to be much more complicated than I anticipated.
Trying to identify how many offices have 10 or more employees didn't work because I can't figure out how to count employees by office:
So I realized I probably need a summarized table where it's giving me each office and the employee count, but am getting consistent errors every time I try to use the GROUPBY or SUMMARIZE function:
returns the error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value". I've tried changing that last COUNT expression to simply using the measure "# Active Employees", and I get the same error.
I also tried a GROUPBY:
We only have 14 offices, 6 of which have 10 or more employees, so this isn't giant complex data.
What am I missing here? Thank you!
Solved! Go to Solution.
The SUMMARIZE function returns a table, but measures must return scalar values. That's why you get the error.
Here's a measure to get the number of offices with more than 10 employees
Offices >10 Employees =
VAR _SelectedNumEmployees = 10
VAR _Result =
COUNTROWS(
FILTER(
VALUES('Employee Directory'[Office - Assigned]),
CALCULATE(COUNT('Employee Directory'[Employee ID])) > _SelectedNumEmployees
)
)
RETURN
_Result
You could replace CALCULATE(COUNT('Employee Directory'[Employee ID])) in the above with a measure that counts number of employees.
@PaulOlding Aha, that worked, thank you so much!! I guess I need to learn more about the VAR function.
@Anonymous Are you creating a measure or a calculated column. You most likely want a measure.
@Greg_Deckler I've tried both, get the same error each time:
This is when I tried it as a column. Same error. I don't understand why it thinks I'm referencing multiple columns.
The SUMMARIZE function returns a table, but measures must return scalar values. That's why you get the error.
Here's a measure to get the number of offices with more than 10 employees
Offices >10 Employees =
VAR _SelectedNumEmployees = 10
VAR _Result =
COUNTROWS(
FILTER(
VALUES('Employee Directory'[Office - Assigned]),
CALCULATE(COUNT('Employee Directory'[Employee ID])) > _SelectedNumEmployees
)
)
RETURN
_Result
You could replace CALCULATE(COUNT('Employee Directory'[Employee ID])) in the above with a measure that counts number of employees.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
23 | |
21 | |
19 | |
13 | |
12 |
User | Count |
---|---|
41 | |
31 | |
25 | |
23 | |
23 |