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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

SUMMARIZE returning errors (cannot convert to scalar value)

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!

1 ACCEPTED 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.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler  I've tried both, get the same error each time: 

wowziewoo_1-1635256680678.png

 

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors