FILTER and SUMMARIZE function error: The expression refers to multiple columns.

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!