The Expression Refers To Multiple Columns. Multiple Columns Cannot Be Converted To A Scalar Value.

[L1102]

Hi All,

I'm looking to calculate 4 tables that list items based on a conbination of conditions based on 2 different averages. Depending on my </> condition will be dependent on what table the items will fall in. Below is what I put in for the first table, but was return with the error mentioned in above. Your help is greatly appriciated.

Red Flag =

VAR AVGSPPD = AVERAGE(Data_Table[$ SPPD])

VAR AVGWTD = AVERAGE(Data_Table[Wghtd Dist])

RETURN

CALCULATETABLE(Data_Table,Data_Table[$ SPPD]<AVGSPPD,Data_Table[Wghtd Dist]>AVGWTD)

[FBergamaschi · Accepted solution]

You must be by mistake being creating a measure

A measure cannot return a table, you should create a new table (Modeling -> New Table) 

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[L1102]

So embarrasing... this is exactly what was happening.

[Zanqueta]

Hello @L1102,

The error “The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value” occurs because within CALCULATETABLE you are attempting to use expressions such as Data_Table[$ SPPD] < AVGSPPD directly. This returns an entire column rather than a logical value for each row.

In DAX, when filtering a table, you need to use functions that evaluate row by row, such as FILTER().

Could you try adpation you code would look like this:

Red Flag =
VAR AVGSPPD = AVERAGE(Data_Table[$ SPPD])
VAR AVGWTD = AVERAGE(Data_Table[Wghtd Dist])
RETURN
CALCULATETABLE(
    Data_Table,
    FILTER(
        Data_Table,
        Data_Table[$ SPPD] < AVGSPPD &&
        Data_Table[Wghtd Dist] > AVGWTD
    )
)

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

[L1102]

Hi @Zanqueta ,

I tried the FILTER expression earlier this morning and it returned the same error.

[GeraldGEmerick]

@L1102 Measures and calculated columns cannot return a table. Only calculated tables can return a table. So your RETURN statement where you are using CALCULATETABLE, that returns a table. You could instead use CALCULATE to return a scalar (single number/text).