Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
DAX
groupby
SUMMARIZE
  • PaulOlding's avatar
    PaulOlding
    4 years ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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. 

     

     

    • PaulOlding's avatar
      PaulOlding
      Solution Sage
      4 years ago

      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.