Row number in a visual table using DAX

Power BI
1

Hi @sam.mckay ,
I’m struggling to find the solution to this apparently simple problem.
I have a huge table coming from a DQ to a SQL with billions of records.
The customer requested a simple report where they can select a subset (usually about 10,000 rows) and download it from the visual to process locally.
The ask is to have each row with an index (1, 2, 3…). Unfortunately, the data has no column that can be used to order and create an index based on a ranking.

Using the table visual hides some lines (see Chair) so adding an index would help also for this.

I tried to add an index in PQ but after many hours it failed.

Is there any way to obtain the row in the visual considering that some rows may have the very same content?

Thanks for your help

Roberto

image

row number on visual.pbix (19.5 KB)

2

Hello Roberto,
You could filter the table in Power Query using a dynamic parameter in M that tells us the number of rows to show in the report.
Here is a link to a Guy in a Cube video that explains how you can do it.

I hope you find it useful.

Greetings

1 Like
3

@Roberto Setup an aggregation table in database with the Index. Since you want to keep the duplicate you can use Window function.

1 Like
4

Hi @jafernandezpuga, thanks for your suggestion. Actually, I keep forgettinh there is also this way to make the report to communicate w/ PQ.
My issue is a bit different though. The customer just wants to see and number on each row, so I tried to apply the pattern I found googling but all of them need a sorting column for ranking.
Now I’m working on the query to the backend from PQ to generate the index column and then be able to rank.

Thanks

Roberto

5

Hi @AntrikshSharma,
I’m sorry but I didn’t get what you propose. Could you please articulate?

Thanks a lot

Roberto

6

Hi @Roberto

So you just need an index column so that the table visual will differentiate the records, it doesn’t have to hold any meaning?

You could add an index in PQ through Add Column → Index Column. Although I’m not sure if DQ will allow this.

If not, you can do it in SQL as @AntrikshSharma mentioned with something like this:

image

And then save that as a SQL view and use that for your datasource. You can tweak the ORDER BY clause to give it more logic if required but it sounds like it doesn’t matter to you what index is assigned to each record.

2 Likes
7

Hi @jamie.bryan,
that’s exactly what I’m doing.

Thanks so much!

Roberto