Token Comma Expected Error

[hibarrbm]

Hi,

I'm receiving the "Token Comma Expected" error for script below.

= CALCULATE ( VAR TotalSalePrice = CALCULATE ( SUMX ( 'ODS_OWNER FACT_ORDER_LINE', IF ( 'ODS_OWNER FACT_ORDER_LINE'[ORDER_LINE_FLG] = 1, 0, 'ODS_OWNER FACT_ORDER_LINE'[TOTAL_SALE_PRICE] ) ) ) RETURN COALESCE ( TotalSalePrice, 0 ) )

Any ideas on how to resolve?

[Anonymous]

[Your Measure] =
CALCULATE(
    0 + SUM ( 'ODS_OWNER FACT_ORDER_LINE'[TOTAL_SALE_PRICE] ),
    KEEPFILTERS( 'ODS_OWNER FACT_ORDER_LINE'[ORDER_LINE_FLG] <> 1 )
)

Your expression is correct. If you have any problems with a formula and syntax, use www.daxformatter.com. I've checked it and it's OK. But your formula is not only unnecessary complex. It's also slower than it could be. The version above is MUCH faster.

[hibarrbm]

THis was the original SQL statement:

  Sum( case when ODS_OWNER.FACT_ORDER_LINE.ORDER_STATUS_KEY in(25,133) then (

            case when (DECODE(BITAND(ODS_OWNER.FACT_ORDER_LINE.ORDER_LINE_FLG,  POWER(2 , 0)),POWER(2 , 0), '1', '0')) = 1 then 0

else ODS_OWNER.FACT_ORDER_LINE.TOTAL_SALE_PRICE

[hibarrbm]

Thank you daxer! this worked perfectly.

I also need to add an extra filter to the statement, i also need to only apply to order status key that = 25 or 133

Does this work:

CALCULATE (

    0 + SUM ( 'ODS_OWNER FACT_ORDER_LINE' [TOTAL_SALE_PRICE]),

    KEEPFILTERS( 'ODS_OWNER FACT_ORDER_LINE' [ORDER_STATUS_KEY] = (25,133)).

    KEEPFILTERS( 'ODS_OWNER FACT_ORDER_LINE' [ORDER_LINE_FLG] <>1).

)

Thanks,

Hugo

[Anonymous]

// I'd shorten the name of the table
// ODS_OWNER FACT_ORDER_LINE. If you
// can shorten the names of the columns
// - even better.

[Your Measure] =
CALCULATE(
    0 + SUM (
        'ODS_OWNER FACT_ORDER_LINE'[TOTAL_SALE_PRICE] 
    ),
    KEEPFILTERS(
        'ODS_OWNER FACT_ORDER_LINE'[ORDER_LINE_FLG] <> 1
    ),
    KEEPFILTERS(
        'ODS_OWNER FACT_ORDER_LINE'[ORDER_STATUS_KEY]
        IN {25, 133}
    )
)

[hibarrbm]

Apologizes, this is the second filter that needs to be applied. Once i updated the formula it gave me the following error message:

Test Sales New = CALCULATE (

0 + SUM ( 'FACT_ORDER_LINE'[TOTAL_SALE_PRICE] ),

KEEPFILTERS ( 'FACT_ORDER_LINE'[ORDER_STATUS_KEY] <> {27,22,20,18,23,19,-2,109,110}),

KEEPFILTERS ( 'FACT_ORDER_LINE'[ORDER_LINE_FLG] <> 1 )

)

[Anonymous]

You can't write
'FACT_ORDER_LINE'[ORDER_STATUS_KEY] <> {27,22,20,18,23,19,-2,109,110}
since the LHS is a scalar and the RHS is a set. When you compare objects they must be of the same type.

The correct condition is
NOT 'FACT_ORDER_LINE'[ORDER_STATUS_KEY] IN {27,22,20,18,23,19,-2,109,110}

[hibarrbm]

Looks like the measure was accepted except now it returns back with zero. any idea why that is?

[lbendlin]

You can always use Daxformatter.com to check your code.  In this case it comes back clean

a =
CALCULATE (
    VAR TotalSalePrice =
        CALCULATE (
            SUMX (
                'ODS_OWNER FACT_ORDER_LINE',
                IF (
                    'ODS_OWNER FACT_ORDER_LINE'[ORDER_LINE_FLG] = 1,
                    0,
                    'ODS_OWNER FACT_ORDER_LINE'[TOTAL_SALE_PRICE]
                )
            )
        )
    RETURN
        COALESCE ( TotalSalePrice, 0 )
)

but the outer CALCULATE() looks suspicious.  I don't think it is required.