topic Re: Token Comma Expected Error in DAX Commands and Tips

Token Comma Expected Error

hibarrbm — Fri, 14 Aug 2020 21:57:07 GMT

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?

Re: Token Comma Expected Error

lbendlin — Fri, 14 Aug 2020 23:30:42 GMT

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.

Re: Token Comma Expected Error

Anonymous — Fri, 14 Aug 2020 23:57:19 GMT

[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.

Re: Token Comma Expected Error

hibarrbm — Sat, 15 Aug 2020 00:09:34 GMT

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

Re: Token Comma Expected Error

hibarrbm — Sat, 15 Aug 2020 00:30:14 GMT

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

Re: Token Comma Expected Error

Anonymous — Sat, 15 Aug 2020 00:30:52 GMT

// 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} ) )

Re: Token Comma Expected Error

hibarrbm — Sat, 15 Aug 2020 00:50:10 GMT

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

Re: Token Comma Expected Error

hibarrbm — Sat, 15 Aug 2020 03:43:05 GMT

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 )

)

Re: Token Comma Expected Error

Anonymous — Mon, 17 Aug 2020 08:24:51 GMT

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}