Forum Discussion
The Expression Refers To Multiple Columns. Multiple Columns Cannot Be Converted To A Scalar Value.
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.
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
5 Replies
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
So embarrasing... this is exactly what was happening.
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).
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 🌀.
