Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Add dynamical incremental column for filtered data

Good day,

 

I have a report with slicers and also with filters applied on the visual. I need to add an incremental column with values from 1 to count(number or rows displayed). This range needs to reset when I change the values of the slicers and a different data set is displayed.

I have tried using index column and other formulas suggested on the forum, but none of them displays the range with consecutive numbers. I get different numbers displayed, for example:

The formula is: Row_Number =
CALCULATE (
COUNT ( MyTable[Index] ),
FILTER ( ALLSELECTED (MyTable ), MyTable[Index] <= MAX ( MyTable[Index] ) )

 

Does anyone have an idea? Thank you.

 

Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi Anonymous ,

     

    I will post the answer here so you can accept and hel others.

     

    Add the following measure and change your Row Number to the code below:

     

    ID_SUM_Group =
SUMX ( MyTable, SUM ( MyTable[Index] ) )

     

    Since there can be more than one index on your context for the table visualization you need to do the following Row Code:

    Row_Number =
VAR Row_Number_Filter = [ID_SUM_Group]
VAR temp_table =
    SUMMARIZE (
        ALLSELECTED ( MyTable),
        MyTable[Column1],
        MyTable[Column2],
        MyTable[column3],
        ...,
        MyTable[ColumnN],
        "Row_number", [ID_SUM_Group]
    )
RETURN
    COUNTROWS ( FILTER ( temp_table, [Row_number] <= Row_Number_Filter ) )

     

    In the summarize function you need to place all the columns you have on the table visualization giving context, be aware that you may not need all of the columns so in some cases you can have a shorter summarize function since so of the columns aren't needed example if you have a ID and ID description you can only use the ID since they both relate to the same information.

     

9 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

     

    Measure = 
CALCULATE(
    COUNT(Sales[Index]),
    FILTER(
        ALLSELECTED(Sales),
        [Index] <= MAX([Index])
    )
)

     

     

    I didn't reproduce your problem, please provide the sample .pbix file or sample data model.

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi Anonymous ,

     

    You need to use a technique similar to the one I used on the post below:

     

    https://community.powerbi.com/t5/Desktop/How-to-add-a-Serial-Row-Number-Column-in-Power-BI-Table-Matrix/m-p/208735

     

    This is similar to your measure however I'm counting the number of rows that preceeds the value, be aware that for this type of solution you need to add the INDEX column no your visualization.

     

    Redoing your measure should be similar to this:

    Row_Number =
CALCULATE (
    COUNTROWS ( MyTable ),
    FILTER ( ALLSELECTED ( MyTable ), MyTable[Index] <= MAX ( MyTable[Index] ) )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi MFelix , amitchandak ,

     

    Thank you for your suggestions. After adding the index and changing the formula I get non consecutive numbers for the column Line Number. For example, it jumps from 10 to 12..

     

     

    • MFelix's avatar
      MFelix
      Super User
      5 years ago

      Hi Anonymous ,

       

      This have to do with context the example I have is based on the entire table so this can cause different issues due to the way the calculations are made.

       

      Can you share a sample data? So I can check what is the adjustments to be made. also can you share the expected result?

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi MFelix  I have sent you the samle data. Thank you.