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

[Miffy]

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!

CaliforniaPR =

ADDCOLUMNS(

    FILTER(

        SUMMARIZE(

            Fact_Sales,

            Dim_Employee[Employee],

            Dim_InvoiceDate[Calendar Year],

            Dim_City[State Province]

        ),

        Dim_City[State Province] = "California"

    ),

    "Profit Ratio",

    [Profit Ratio]

)

[Miffy]

Hi @grazitti_sapna 

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 · Accepted solution]

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!