Forum Discussion

hibarrbm's avatar
hibarrbm
Helper I
6 years ago

Token Comma Expected Error

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?

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    [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's avatar
      hibarrbm
      Helper I
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago
        // 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's avatar
      hibarrbm
      Helper I
      6 years ago

      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

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    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.