CODEWITHSUNDEEP
powerbidax
h04phg
h04phg

Reputation: 15

DAX problem "The expression refers to multiple columns. It cannot be converted to a scalar value."

I have an issue with a DAX formula in Power BI. I want to calculate how our purchase share for each country evolves over the years. I have tried to write a DAX formula but received the following error message:

'The expression refers to multiple columns. It cannot be converted to a scalar value.

I have tested this formula:

Purchase Share per Country =
VAR TotalIn2022 =
    SUMX (
        FILTER ( 'Table1', 'Table1'[Order Date].[Year] = 2022 ),
        'Table1'[Total Amount (HKD)]
    )
VAR TotalIn2023 =
    SUMX (
        FILTER ( 'Table1', 'Table1'[Order Date].[Year] = 2023 ),
        'Table1'[Total Amount (HKD)]
    )
RETURN
    ADDCOLUMNS (
        SUMMARIZE ( 'Table1', 'Table1'[Factory Country] ),
        "Purchase Share 2022",
            DIVIDE (
                SUMX (
                    FILTER ( 'Table1', 'Table1'[Order Date].[Year] = 2022 ),
                    'Table1'[Total Amount (HKD)]
                ),
                TotalIn2022
            ),
        "Purchase Share 2023",
            DIVIDE (
                SUMX (
                    FILTER ( 'Table1', 'Table1'[Order Date].[Year] = 2023 ),
                    'Table1'[Total Amount (HKD)]
                ),
                TotalIn2023
            )
    )

But I'm encountering an error message: 'The expression refers to multiple columns. It cannot be converted to a scalar value.

What do you think, can this problem be resolved?

Upvotes: 1

Views: 10365

Answers (1)

Marcus
Marcus

Reputation: 4015

It looks like you are trying to save a table expression as a measure. A measure requires a scalar output, but if the measure expression results in a columnar or table-like output, you will usually get this type of error.

Try the expression in the new table dialog, instead of a new measure dialog.

Upvotes: 2

Related Questions