Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Trying to create a dynamic row number in a table visual

I need to add a Row number to one of my table visuals. I've created a unique index column in the underlying table. The index can be used to create a row number starting from Row 1. Unfortunately when I filter the table visual on the Year column the Row value no longer starts with row 1. Below is what is coming out in the table visual. Is there a way to define a dynamic row number so it always starts with a row 1 regardless of the filtering on the visual?

 

 

Need Help
  • sevenhills's avatar
    sevenhills
    1 year ago

    Two points if known, can provide accurate syntax.

     

    Use case: you are trying to get the row number based on filter selection of each row. 

     

    Assuming you have index column to identify unique row, then you can use this.

     

    Try this:

    Row_Number = 
CALCULATE (
    COUNTROWS('Table');
    FILTER ( ALLSELECTED ('Table'), 'Table'[Index] <= MAX ( 'Table'[Index]) )
)

     

    Or  If not you can use all the filters that are applied to the table.

     

    Row_Number = 
var _v1 = SELECTEDVALUE ( 'Table'[filterColumn1])
var _v2 = SELECTEDVALUE ( 'Table'[filterColumn2])
RETURN CALCULATE ( COUNTROWS('Table');
    FILTER ( ALLSELECTED ('Table'),
           'Table'[filterColumn1] <= _v1 && 'Table'[filterColumn2] = _v2 )
)

     

    Hope this helps!

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      I tried 

      Row = ROWNUMBER('Table_name')  The Tabe_name is the table behind the visual. Unfortunaely it only returns the value 1 for each row. 
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      I'm getting this error.

      S. No. = ROWNUMBER(ALLSELECTED('C-Standard Revisions'),ORDERBY('c-standard Revisions'[BOOK_DATE]))

       

  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper User
    1 year ago

    Two points if known, can provide accurate syntax.

     

    Use case: you are trying to get the row number based on filter selection of each row. 

     

    Assuming you have index column to identify unique row, then you can use this.

     

    Try this:

    Row_Number = 
CALCULATE (
    COUNTROWS('Table');
    FILTER ( ALLSELECTED ('Table'), 'Table'[Index] <= MAX ( 'Table'[Index]) )
)

     

    Or  If not you can use all the filters that are applied to the table.

     

    Row_Number = 
var _v1 = SELECTEDVALUE ( 'Table'[filterColumn1])
var _v2 = SELECTEDVALUE ( 'Table'[filterColumn2])
RETURN CALCULATE ( COUNTROWS('Table');
    FILTER ( ALLSELECTED ('Table'),
           'Table'[filterColumn1] <= _v1 && 'Table'[filterColumn2] = _v2 )
)

     

    Hope this helps!

    • sevenhills's avatar
      sevenhills
      Icon for Super User rankSuper User
      1 year ago

      Just to add some samples:

      Using selected values as guidance:


      Using Index (as guidance):

      Default output:

      Selected filter output:

       


      Is this what you are looking for ? Hope this helps!