Forum Discussion

Clint's avatar
Clint
Helper V
6 years ago
Solved

Question about error in Measure: Expression refers to multiple columns

Hello,

 

Not sure why this measure returns the error "The expression refers to multiple columns.  multiple colulmns cannot be converted to a scalar value. 

 

Task Percent Completed for Customer Program =
CALCULATE(
    FILTER('Tasks','Tasks'[Task Percent Completed]),
    'Tasks'[Task Name] IN { "Customer Program" }
)
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Clint ,

     

    As we know, we cannot create dynamic calculated table/ column. Here we can use table visual and a measure to work around.

    Measure = var a = MAX('Table'[Task Name])
return
IF(a  in  { "Customer Program" },"yes",BLANK())

     

  • Clint's avatar
    Clint
    6 years ago

    Thank you.  What ended up work is:

    M_Program Progress =

    DIVIDE(
    Value(
    MAXX(
    CALCULATETABLE('Tasks',
    'Tasks'[Task Name]="Customer Program"),
    'Tasks'[Task Percent Completed])),100)

7 Replies

  • lc_finance's avatar
    lc_finance
    Solution Sage
    6 years ago

    Hi Clint ,

     

    the reason is that the FILTER formula returns a table as a result, while the CALCULATE formula should be used with a single number (single numbers are called 'scalar values' in Power BI).

     

    What exactly do you want to obtain with the formula?

     

    Regards,

     

    LC

    www.finance-bi.com

    • Clint's avatar
      Clint
      Helper V
      6 years ago

      Shouldn't the filters there return a one cell table?  I was hoping to return the value for percent complete for that one task in that one project

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Please check the Filter function. What is the second parameter doing in Filter function? It is not Filtering anything, it is supposed to filter the Task table.
  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi Clint ,

     

    Please update your formula as below.

    Task Percent Completed for Customer Program =
CALCULATE (
    COUNTROWS ( 'Tasks' ),
    FILTER ( 'Tasks', 'Tasks'[Task Name] IN { "Customer Program" } )
)
    • Clint's avatar
      Clint
      Helper V
      6 years ago

      Hello,

       

      This 

      CALCULATE (
    COUNTROWS ( 'Tasks' ),
    FILTER ( 'Tasks', 'Tasks'[Task Name] IN { "Customer Program" } )
)

      just returns the count of 1 telling me there is only one task with that name.  What I need to return is the value of the "Task percent Complete" for that task 

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi Clint ,

     

    As we know, we cannot create dynamic calculated table/ column. Here we can use table visual and a measure to work around.

    Measure = var a = MAX('Table'[Task Name])
return
IF(a  in  { "Customer Program" },"yes",BLANK())

     

    • Clint's avatar
      Clint
      Helper V
      6 years ago

      Thank you.  What ended up work is:

      M_Program Progress =

      DIVIDE(
      Value(
      MAXX(
      CALCULATETABLE('Tasks',
      'Tasks'[Task Name]="Customer Program"),
      'Tasks'[Task Percent Completed])),100)