DAX - Create Dynamic Index Column

Question

I am trying to create a calculated column in SSAS tabular model with DAX. I want a dynamic index column on a table. Meaning that the index starts at 0 when the table is filtered. Imagine you have a table like:

item      index
apple     0
banana    1
celery    2
broccoli  3

If I filter the table to just vegetables normally the index would still be:

item      index
celery    2
broccoli  3

But I want it to be

item      index
celery    0
broccoli  1

So far I create the index with: (I am indexing a date dimension table)

=CALCULATE(COUNT([Date])-1, ALL('DimDate'), FILTER(DimDate, [Date]<=EARLIER([Date])))

I have tried using ALLEXCEPT() and I have tried making an Offset column by getting the first value of the Index with FIRSTNONBLANK, but have not yet been successful.

Any ideas or help?

Rory Rory · Accepted Answer · 2016-07-29T09:14:51

You cannot have a dynamic calculated column because calculated columns are evaluated and stored at design time. So you would have to use a measure to do the dynamic indexing. Here is one approach below using ALLSELECTED with additional parsing to display blank as zero.

=
IF (
    ISBLANK (
        CALCULATE (
            COUNTROWS ( DimDate ),
            FILTER ( ALLSELECTED ( DimDate ), DimDate[Date] < MAX ( DimDate[Date] ) )
        )
    ),
    0,
    CALCULATE (
        COUNTROWS ( DimDate ),
        FILTER ( ALLSELECTED ( DimDate ), DimDate[Date] < MAX ( DimDate[Date] ) )
    )
)

DAX - Create Dynamic Index Column

1 Answers