Count Rows for Table Visualization with Multiple Fields

[Angela_Rom]

I am looking to create a measure or column in which the row numbers are from 1,2, 3, 4, 5....etc based on the table visualization that I have created. The table visualization has multiple filters and I would like the numbers to only reflect the rows within the table with filters set. 

I have created a measure: ROW NUMBER = CALCULATE(COUNTROWS('EXPECTED RECEIPTS'), ALLSELECTED('EXPECTED RECEIPTS'[COM.PONUMBER])). But it does not count the rows in a cardinal order. Basically, I would like to add the same feature as shown in Excel's view option under heading. I do not want the numbers to match with anything but represent each row location within the table.

[Angela_Rom]

Hi Everyone,

I am still looking for a solution to this issue of row count. This project was set aside for a while and now I am revisiting this report. I need the row count based on a filtered table view that the end user would create based on slicers as well as report filters. I have a slicer for the column : warehouse and the column: buying method is a report level filter. There are other filters that the user can apply but regardless, the report must show a row count based on the table after filters are applied. I have tried count rows and index but the numbers not showing me a count as listed below.

Table:

Part Number   Warehouse              Delivery Date         Order Number      Buying Method

A                             1                              11/1/18                6761                       1

B                             2                              11/2/18                7549                       2                             

C                             3                              12/2/18                4497                       3             

D                             4                              11/3/18                3321                       4

Filtered Table View showing Row # - Row number would be based on row count for that filtered table.

Row #         Part Number                  Warehouse          Delivery Date    Order Number   Buying Method

1                              E                              1                              11/1/18                1234                       1

2                              F                              1                              11/2/18                5678                       2                             

3                              G                             1                              12/2/18                9779                       2             

4                              G                             1                              11/3/18                8822                       4   

Any help that can be provided, would be greatly appreciated.

Regards,

Angela           

[v-jiascu-msft]

Hi @Angela_Rom,

How about adding an index in the Query Editor since you don't want the numbers to match with anything? 

Best Regards,
Dale

[Angela_Rom]

I have tried the Index column but when i inserted the index column in the table visualization, it did not show numbering in the cardinal order that I need and it seemed to give me numbering based on the whole query, where there are filters on the table side. Is there a workaround or was there a measurement that should be applied to the Index column on the table visualization side?

[v-jiascu-msft]

Hi @Angela_Rom,

Do you mean you'd like the order of the filtered table values? For example, there is always a column that is 1, 2, 3... whatever the filter is. So which values or columns should it be based on?

Best Regards,
Dale

[Angela_Rom]

Hi Dale,

Yes, the order should be based on the filtered table values.  The column would be the PO number column shown in the table visualization. The issue I am experiencing with the measurement is that instead of producing 1,2,3,4, it seems to be showing me a row number count for each PO number.

Regards,

Angela

[v-jiascu-msft]

Hi Angela,

What's the original data of [Row Number]? Can you show it by changing the aggregation to "Don't summarize"? If not, you can try the function RANKX to create a dynamic row number.

RowNumber =
CALCULATE (
    ALLSELECTED ( 'table'[Row Number] ),
    CALCULATE ( COUNT ( 'table'[Row Number] ) )
)

Best Regards,
Dale

[Angela_Rom]

Hi Dale,

Row number is a measure that I created which counted rows of the column, "PONUMBER" within the table "Expected Receipts". I used this column because each "PONUMBER" is unique. My measure for "Row Number" was using Count Rows but it was not counting the rows based on the filters that was applied in this table visualization. The table "Expected Receipts" is not filtered because I need all the data within the table to provide different views which I filter within the table visualization.

I changed the measure  for Row Number to reflect what you had suggested but the count is just showing 1. I need the Row number to give me the count based how many rows are actually in the table.

Below is the screenshot for the adjusted measure:

[v-jiascu-msft]

Hi @Angela_Rom,

Can you share a sample file? Please mask the sensitive parts first.

Best Regards,
Dale

[Angela_Rom]

Hi Dale,

Sorry about the delayed response. What information would you need in the sample file? This issue is still unresolved. Also, I want to note that I have buttons for two different views.  The table is one file but due to sorting restrictions, I had to create two different views of the table. This is why I need a row count that is based on the records within the specific table visual and not on the data as a whole.

Regards,

Angela