topic Re: SUMMARIZE returning errors (cannot convert to scalar value) in DAX Commands and Tips

SUMMARIZE returning errors (cannot convert to scalar value)

Anonymous — Tue, 26 Oct 2021 11:40:13 GMT

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:

# Offices with 10 or more employees =

if([# Active Employees]>=10,

DISTINCTCOUNT('Employee Directory'[Office - Assigned]),0)

This just returns a count of offices, because it's evaluating whether the whole company has 10 or more employees.

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:

Employees per office =

SUMMARIZE('Employee Directory','Employee Directory'[Office - Assigned],

"# per office",

COUNT('Employee Directory'[EmployeeID]))

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:

# employees by office =

GROUPBY('Employee Directory','Employee Directory'[Office - Assigned],

"# per office",sumx(CURRENTGROUP(),'Employee Directory'[# Active Employees]))

and got the same "scalar" error.

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!

Re: SUMMARIZE returning errors (cannot convert to scalar value)

Greg_Deckler — Tue, 26 Oct 2021 12:18:25 GMT

Anonymous Are you creating a measure or a calculated column. You most likely want a measure.

Re: SUMMARIZE returning errors (cannot convert to scalar value)

Anonymous — Tue, 26 Oct 2021 13:58:39 GMT

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.

Re: SUMMARIZE returning errors (cannot convert to scalar value)

PaulOlding — Tue, 26 Oct 2021 14:34:32 GMT

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.

Re: SUMMARIZE returning errors (cannot convert to scalar value)

Anonymous — Tue, 26 Oct 2021 15:00:20 GMT

PaulOlding Aha, that worked, thank you so much!! I guess I need to learn more about the VAR function.