CODEWITHSUNDEEP
powerbidax
Synik
Synik

Reputation: 141

DAX question - Multiple columns cannot be converted to a scalar value error with Calculate

I have looked at other topics, but I cannot seem to apply my situation to them.

I'm doing a CALCULATE statement and getting an error about multiple columns. While this seems like a simple error, I have attempted to remove all instances of multiple columns, yet the error remains. Here is my code:

IF (
    FILTER ( 'ENTERPRISE VIEW', [Total Prod Segment Check] = "1" ),
    CALCULATE (
        SUM ( 'ENTERPRISE VIEW'[RPC] )
            + SUM ( 'ENTERPRISE VIEW'[New Business] )
            + SUM ( 'ENTERPRISE VIEW'[Retention Premium ] )
            + SUM ( 'ENTERPRISE VIEW'[CurrentTermPremium] )
            - SUM ( 'ENTERPRISE VIEW'[Retention Premium ] ),
        'ENTERPRISE VIEW'[EffectiveDate] = YEAR ( NOW () )
    ),
    IF (
        FILTER ( 'ENTERPRISE VIEW', 'ENTERPRISE VIEW'[EffectiveMonth&Year].[Year] )
            = YEAR ( NOW () )
            && FILTER ( 'ENTERPRISE VIEW', [Total Prod Segment Check] = "0" ),
        CALCULATE (
            SUM ( 'ENTERPRISE VIEW'[RPC] )
                + SUM ( 'ENTERPRISE VIEW'[New Business] )
                + SUM ( 'ENTERPRISE VIEW'[Retention Premium ] ),
            'ENTERPRISE VIEW'[EffectiveDate] = YEAR ( NOW () )
        ),
        0
    )
)

What I'm trying to do is add up various metrics if the business name is either 1 or 0, then filter based on the current year.

For reference, I'm using Power BI and I created a column with this logic working here:

VAR _A = 'ENTERPRISE VIEW'[RPC]
       + 'ENTERPRISE VIEW'[New Business]
       + 'ENTERPRISE VIEW'[Retention_Premium]
       + 'ENTERPRISE VIEW'[CurrentTermPremium]
       - 'ENTERPRISE VIEW'[Retention_Premium]

VAR _B = 'ENTERPRISE VIEW'[RPC]
       + 'ENTERPRISE VIEW'[New Business]
       + 'ENTERPRISE VIEW'[Retention_Premium]
RETURN
    IF (
        'ENTERPRISE VIEW'[EffectiveMonth&Year].[Year] = YEAR ( NOW () )
            && 'ENTERPRISE VIEW'[Total Prod Segment Check] = "1",
        _A,
        IF (
            'ENTERPRISE VIEW'[EffectiveMonth&Year].[Year] = YEAR ( NOW () )
                && 'ENTERPRISE VIEW'[Total Prod Segment Check] = "0",
            _B,
            0
        )
    )

But I'm trying to take the same logic in the column and apply it to a measure. I'm just not sure why this error is occurring.

I appreciate the help!

Upvotes: 0

Views: 936

Answers (2)

Synik
Synik

Reputation: 141

Thanks @Alexis,

This required a small modification to the year filter part of the CALCULATE.

CALCULATE(
    SUM('ENTERPRISE VIEW'[RPC]) + SUM('ENTERPRISE VIEW'[New Business]) + SUM('ENTERPRISE VIEW'[Retention Premium ]) + (SUM('ENTERPRISE VIEW'[CurrentTermPremium]) - SUM('ENTERPRISE VIEW'[Retention Premium ]))
    ,'ENTERPRISE VIEW'[Total Prod Segment Check] = "1"
    ,YEAR('ENTERPRISE VIEW'[EffectiveDate]) = YEAR(NOW())
)
+ 
CALCULATE(
    SUM('ENTERPRISE VIEW'[RPC]) + SUM('ENTERPRISE VIEW'[New Business]) + SUM('ENTERPRISE VIEW'[Retention Premium ])
    ,'ENTERPRISE VIEW'[Total Prod Segment Check] = "0"
    ,YEAR('ENTERPRISE VIEW'[EffectiveDate]) = YEAR(NOW())
)

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40204

@smpa is correct. FILTER ( 'ENTERPRISE VIEW', [Total Prod Segment Check] = "1" ) is an entire table with multiple columns and it doesn't make sense to say if <Table> then ....

If you want your measure to be equivalent to summing that calculated column, then I think you can write it like this:

CALCULATE (
    SUM ( 'ENTERPRISE VIEW'[RPC] )
        + SUM ( 'ENTERPRISE VIEW'[New Business] )
        + SUM ( 'ENTERPRISE VIEW'[CurrentTermPremium] ),
    'ENTERPRISE VIEW'[EffectiveDate] = YEAR ( NOW () ),
    'ENTERPRISE VIEW'[Total Prod Segment Check] = "1"
) +
CALCULATE (
    SUM ( 'ENTERPRISE VIEW'[RPC] )
        + SUM ( 'ENTERPRISE VIEW'[New Business] )
        + SUM ( 'ENTERPRISE VIEW'[Retention Premium ] ),
    'ENTERPRISE VIEW'[EffectiveDate] = YEAR ( NOW () ),
    'ENTERPRISE VIEW'[Total Prod Segment Check] = "0"
)

(Note that [Retention Premium ] cancels itself out in the first CALCULATE.)

Upvotes: 1

Related Questions