Creating index column to count rows

[dokat]

Hi I have a .pbix file from direct query data feeds from axure server.

I created a report using four tables that are linked in the back end.

The name of the tables are  Activity Status, Group, Families, Division. 

I created measures like Revenue, Units ie under Activity Status table and bring different atttributes from other 3 tables. How can i create index measure and calculate number of rows in the report?

Thanks

[AbhinavJoshi]

Hi @dokat. You can create an index column in Power Query M. I hope it helps! 

[dokat]

@AbhinavJoshi Thank you for your response. I am connected to the server via direct query to pull the data.I dont have access or visibility to query editor.  Is there a measure i can use, rankx didnt work for me because data is coming from 4 different tables, not from same table.

Thanks

[AbhinavJoshi]

Hi, I see the situation now. Create a new column in DAX, and enter this formula 

Index =

    COUNTROWS(

        FILTER(

            ALL('TableName'),

            'TableName'[UniqueKey] <= EARLIER('TableName'[UniqueKey])

        )

    )

[dokat]

@AbhinavJoshi i have four tables that are linked which table should i reference to? It gives below error message.

EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

[AbhinavJoshi]

The report is based on one table or combined of two or more tables? 

[dokat]

@AbhinavJoshi Its combined of 4 tables.

[AbhinavJoshi]

In that case, if you have any fact tables create and index on that, or simply add a card on the Visualization that shows the count of your ID. 

[dokat]

@AbhinavJoshi I am still getting the same error message

EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

"

[AbhinavJoshi]

Hi, try this if earlier doesn't work for you

Index =

    COUNTROWS(

        FILTER(

            ALL('TableName'),

            'TableName'[UniqueKey] <= MAX('TableName'[UniqueKey])

        )

    )

[dokat]

@AbhinavJoshi this only returned number of rows in Parent Customer table. One customer may show up multiple times in the report because they can carry multiple product line.

Please see below example. Walmart carries 200 different product lines because of that show up 200 times in the report. However above formula only return unique count of customers in " Parent Customer" table. I am trying to find out total number of rows (basically number of customer product combinations)

Hope this makes sense

Customer	Item
Walmart	101
Walmart	102
Target	101
Kroger	103
H-E-B	103
H-E-B	104