Add dynamical incremental column for filtered data

[Anonymous]

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.

[v-lionel-msft]

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.

[Anonymous]

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]

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]

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

[MFelix]

Hi @Anonymous ,

I have sent you a private message with the answer can you please check it and tell me if it works so I can post it here so it can help others.

[Anonymous]

Hello @MFelix , sorry for the delayed reply. I have tested the fix and it works, thank you very much.

[MFelix · Accepted solution]

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.

[MFelix]

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] ) )
)

[amitchandak]

@Anonymous , this is the correct formula and should respond to changes with a filter. Can share a screenshot of the issue.

Also, vote for the idea

Serial No in Visual Table
https://ideas.powerbi.com/ideas/idea/?ideaid=80612ec5-14a9-4929-a2ee-531943fa51d4