DAX measure which returns an index (counter) in a table depending on the displayed table items

[freidav1984]

Hi All!

I have a challenge, I would like to display a counter value in table visual, which should return an increasing number (increment +1) depending on the displayed and filtered items shown in the table visual.

I have a calculated table, which contains invoices' data.

ID	Partner	Category - Main	Category - Sub
AX1	A	A	A
AV6	B	A	B
ZT1	C	A	C
DS2	D	B	A
AS7	D	B	B

If I put all data fields from the above shown table into a table visualization, I would like to add a measure which returns an index or counter value based on how the table is filtered. What I want to achieve.

Example 1.:

If I filter the table for category - main = 'A' then I would need the following result in the table visualization

ID	Partner	Category - Main	Category - Sub	MEASURE - Index
AX1	A	A	A	1
AV6	B	A	B	2
ZT1	C	A	C	3

Example 2.:

If I filter the table for category - main = 'B' then I would need the following result in the table visualization

ID	Partner	Category - Main	Category - Sub	MEASURE - Index
DS2	D	B	A	1
AS7	D	B	B	2

Do you have any solution on this issue?

Thanks a lot!

David

[v-rzhou-msft · Accepted solution]

Hi @freidav1984 ,

I suggest you to try this code to create a measure.

Dynamic Index = 
VAR _1 =
    ADDCOLUMNS (
        ALLSELECTED ( 'Table' ),
        "Merage", COMBINEVALUES ( " ", [Category - Main], [Category - Sub] )
    )
VAR _2 =
    ADDCOLUMNS ( _1, "Index", RANKX ( _1, [Merage],, ASC, DENSE ) )
RETURN
    SUMX ( FILTER ( _2, [ID] = MAX ( 'Table'[ID] ) ), [Index] )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.