Forum Discussion
FILTER and SUMMARIZE function error: The expression refers to multiple columns.
Hello Power BI experts,
I've been stuck with the following error message: 'The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.' Despite my efforts, including using ChatGPT, I haven't been able to resolve it. I would really appreciate any insights or guidance from the community. Thank you in advance!
Hi Miffy,
This type of error generally happens when your measure or expression in your ADDCOLUMNS function returns more than one column or value, and not a single scalar value for every row. In your instance, it's probably that the [Profit Ratio] measure isn't being considered in an appropriate row context, or its formula is making it return multiple values. A simple solution is to enclose your measure in CALCULATE to apply the correct filter context. For instance, you could change the ADDCOLUMNS section like this:
CaliforniaPR =
ADDCOLUMNS(
FILTER(
SUMMARIZE(
Fact_Sales,
Dim_Employee[Employee],
Dim_InvoiceDate[Calendar Year],
Dim_City[State Province]
),
Dim_City[State Province] = "California"
),
"Profit Ratio", CALCULATE([Profit Ratio])
)🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
2 Replies
Thank you for your quick response! Much appreciated.
Thanks to your comment, I now realize what went wrong. I was initially entering the formula on a column level, whereas I should have applied this at the table level. It now works!
Thank you again!
- grazitti_sapna
Super User
Hi Miffy,
This type of error generally happens when your measure or expression in your ADDCOLUMNS function returns more than one column or value, and not a single scalar value for every row. In your instance, it's probably that the [Profit Ratio] measure isn't being considered in an appropriate row context, or its formula is making it return multiple values. A simple solution is to enclose your measure in CALCULATE to apply the correct filter context. For instance, you could change the ADDCOLUMNS section like this:
CaliforniaPR =
ADDCOLUMNS(
FILTER(
SUMMARIZE(
Fact_Sales,
Dim_Employee[Employee],
Dim_InvoiceDate[Calendar Year],
Dim_City[State Province]
),
Dim_City[State Province] = "California"
),
"Profit Ratio", CALCULATE([Profit Ratio])
)🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
