Promotion: Use code DAXERROR to save 10% off my course. Module 1 is free.
Sometimes, when working with DAX, you might get the following error:
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
This error occurs whenever the DAX engine was expecting a single value, or scalar, and instead received a table of values instead. This is an easy error to make because many DAX functions, such as FILTER, SUMMARIZE and ALL, return table values. There are three situations where this error commonly occurs:
- Assigning a table value to a measure or calculated column
- Forgetting to use a DAX aggregation
- Treating ALL or FILTER as an action, not a function
In the rest of the post, we’ll cover each scenario and how to fix it.
Assigning a table value to a measure or calculated column
Let’s say that you were doing some analysis on the products table in the AdventureWorks sample database. In this case, maybe you want to only look at the black products. So you create a measure with the following code:
BlackProducts = FILTER(Products, Products[Color] = “Black”)
One solution to this problem is instead of assigning the code to a measure, which is intended to display a single value, you can create a calculated table instead.
To do so, go to Modeling –> New table in Power BI Desktop. Then ender the same code as before but for the calculated table. Now you will see a table filtered accordingly.
Forgetting to use a DAX aggregation
Now, what if we actually did want a single value instead of a table? Let’s say we want to count the number of black products. In that case, we could wrap our code in an aggregation function, such as COUNTROWS which can take in a table and return a single value.
CountOfBlackProducts = COUNTROWS(FILTER(Products, Products[Color] = “Black”))
This code will return the count of all products, but only if they have black as the color.
Treating ALL or FILTER as an action, not a function
Sometimes, people will try to use functions like ALL or FILTER to filter information on the report. By themselves, these functions actually return a table. However, when they are used with CALCULATE and CALCULATETABLE then you can use them to filter your data appropriately.
Want to learn more?
If you want to learn more about DAX, then check out my free learning path and my paid Pluralsight course.
