Forum Discussion

wynrod's avatar
wynrod
Frequent Visitor
5 years ago

Add consecutive incremental counter in filtered table

Hi All,

 

I've been searching the community for examples of a similar request but can't find one relevant to the scenario I have.

 

I need to add a column which counts upwards 1,2,3,4 etc, but the values need to be dynamic so no matter if a filter is applied to a table, the increments are still 1 each time. The aim is to get a unique, consequtive record ID for every record in the filtered table

 

Unfiltered:

CustomerID Column
A1
B2
C3
D4
E5

 

Filtered:

CustomerID Column
A1
D2
E3

 

Many thanks in advance,

 

Nick

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    wynrod , Try a new measure

     

    countx(filter(allselected(Table), Table[CustomerID] <=max(Table[CustomerID])),Table[CustomerID])

    • wynrod's avatar
      wynrod
      Frequent Visitor
      5 years ago

      Hi amitchandak ,

       

      Thanks for your quick reply.

       

      Your suggestion worked great with small datasets but as soon as it's scaled-up to work with larger volumes (100k+), the measure prevents the data being displayed due to the time it cases to compute the values (both in desktop and published service).

       

      Do you know if using a variable would be more efficient? I've spent time trying to put something in place using variables but haven't found a way yet.

       

      Cheers,

       

      Nick

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi wynrod ,

     

    Try

    Measure = CALCULATE(COUNTROWS('Table'),FILTER(ALLSELECTED('Table'),'Table'[Column1]<=MAX('Table'[Column1])))

    Or

    Measure 2 = MAX('Table'[Column1])

Measure 3 = RANKX(ALLSELECTED('Table'),[Measure 2],,ASC,Dense)

     

    Best Regards,

    Jay