Dax - Syntax error for variable in Calculated Column

Question

I'm busy learning how to use variables in DAX formulas in PowerPivot for Excel 2013.

When put this formula in a calculated column

=
VAR
    CurrentPrice = Product[Unit Price]
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( Product[Unit Price] ),
            Product[Unit Price] > CurrentPrice
        )
    ) + 1

I'm getting the following error:

The syntax for 'CurrentPrice' is incorrect.
The calculated column 'Product[CalculatedColumn1]' contains a syntax error. Provide a valid formula.

I can't figure out what is the problem with this formula.

I'm using a contoso.xlsx sample workbook.

score 2 · Accepted Answer · answered Jan 12 '16 at 17:59

Power Pivot for Excel 2013 does not support DAX variables. This feature is available in Power Pivot for Excel 2016 and Power BI Desktop.

You can rewrite this specific calculated column to use EARLIER():

=COUNTROWS (
    FILTER (
        VALUES ( 'Product'[Unit Price] ),
        'Product'[Unit Price] > EARLIER( 'Product'[Unit Price] )
    )
) + 1

Alternately, you can do away with FILTER() entirely:

=CALCULATE(
    DISTINCTCOUNT( 'Product'[Unit Price] )
    ,ALL( 'Product' )
    ,'Product'[Unit Price] > EARLIER( 'Product'[Unit Price] )
) + 1

Dax - Syntax error for variable in Calculated Column

1 Answers1