cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

How can I create a simple index/ row number which is dynamic

Hi All,

I have a product table with ID, DATE, PRODUCT ,Count of  products and index across the table.

 ID date Product name count Index A001 1/1/2023 Apple 1 1 B002 1/2/2023 Samsung 4 2 C003 1/3/2023 Apple 5 3 D004 1/4/2023 Microsoft 2 4 E005 1/5/2023 Google 3 5 F006 1/6/2023 Apple 6 6

How to create a dynamic index which auto assigns the series when a filter is applied. For example, product name = 'Apple'
Output :

 ID date Product name count Index A001 1/1/2023 Apple 1 1 C003 1/3/2023 Apple 5 2 F006 1/6/2023 Apple 6 3

Appreciate for your help!

Thanks

1 ACCEPTED SOLUTION
Super User

Hi,

I assume you want to create a meausure, not a calculated column.

Please check the below picture and the attached pbix file.

I authored a measure that results the index number, and it is depending on the date and the ID column.

Please kindly correct me if the logic to create the index number is different.

``````Index measure: =
COUNTROWS (
WINDOW (
1,
ABS,
0,
REL,
SUMMARIZE (
ALLSELECTED ( Data ),
Data[ID],
Data[date],
Data[Product name],
Data[count]
),
ORDERBY ( Data[date], ASC, Data[ID], ASC )
)
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

2 REPLIES 2
Community Support

Hi  @KN8 ,

Here are the steps you can follow：

1. Create measure.

``````Measure =
RANKX(
ALLSELECTED('Table'),CALCULATE(MAX('Table'[date])),,ASC)``````

2. Result:

Best Regards,

Liu Yang

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

Super User

Hi,

I assume you want to create a meausure, not a calculated column.

Please check the below picture and the attached pbix file.

I authored a measure that results the index number, and it is depending on the date and the ID column.

Please kindly correct me if the logic to create the index number is different.

``````Index measure: =
COUNTROWS (
WINDOW (
1,
ABS,
0,
REL,
SUMMARIZE (
ALLSELECTED ( Data ),
Data[ID],
Data[date],
Data[Product name],
Data[count]
),
ORDERBY ( Data[date], ASC, Data[ID], ASC )
)
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.