Line Number in Table

[Bryanna]

Hi,

Looking for some help! I need to add a line number to a table in PBI, however, everything i try is too slow to populate even when i have it filtered with just a handful of options. I created an index in power query and then used the below measure. I need this to be dynamic when they filter in the report and i only have the order number to rank off of. Any tips would be appreciated!

Row number = CALCULATE(COUNTROWS('Order Rank'),FILTER(ALLSELECTED('Order Rank'),'Order Rank'[Rank]<=MAX('Order Rank'[Rank])))

[v-dineshya]

Hi @Bryanna ,

Thank you for reaching out to the Microsoft Community Forum.

You are trying to create a dynamic row number in Power BI when filter in the report based on "Order  Number".

Please follow below steps.

1.  I have created table "Table" based on your sample data. refer snap.

2.  Created measure "Row Number" with below DAX , it will create dynamic row number.

Row Number =

CALCULATE(

    COUNTROWS(

        FILTER(

            ALLSELECTED('Table'[Order Number]),

            'Table'[Order Number] <= MAX('Table'[Order Number])

        )

    )

)

3.  Please refer output snap and attched PBIX file for your reference.

If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.  Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @Bryanna ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Thank you.

[v-dineshya]

Hi @Bryanna ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Thank you.

[Bryanna]

Apologies on the delay. We ended up having to do a new visual calculation and entered a 1 and then did a runningsum calculation off of that. The only stipulation is there is no total at the bottom.

What you had provided worked for a small number of entries but due to the amount of data in the pbi , it wouldnt work for this scenario.

[v-dineshya · Accepted solution]

Hi @Bryanna ,

Thank you for your response. Please share sample PBIX file. It will help me to replicate the scenario.

Regards,

Dinesh

[v-dineshya]

Hi @Bryanna ,

We haven’t heard from you on the last response and was just checking back . Please share sample PBIX file. It will help me to replicate the scenario.

Regards,

Dinesh

[FBergamaschi]

Plase clarify if you are looking for a calculated column (a static thing) or a measure (dinamic thing). Assume you are looking for a measure but just to be sure. 

More importantly, please provise a sample of the table and a clear explanation of what you want to rank: you want to rank the order nr in an asceding way? And you want the ranking to be based only on the visible values in the matrix?

And what is the issue you have with your formula in use?

Best

FB

[Bryanna]

I keep receiving this error but there are no duplicate order numbers in the table.

A single value for column 'Order Number' in table 'Order Rank' cannot be determined. This can happen when a measure or function formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[FBergamaschi]

This error is due to a missing row context, please check v-dineshya answer, maybe that solved already

[MasonMA]

@Bryanna , Hi, you may try 

Row Number = 
RANKX(
    ALLSELECTED('Order Rank'),
    'Order Rank'[Order Number],
    ,
    ASC,
    DENSE
)