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

Helper I

## Conditional Index with Maximum Value

Hi everyone! I have been searching the forum for conditional index solutions but I can't seem to find any which matches my problem. It seems fairly simple yet I wasn't able to combine ideas from provided solutions (such as usage of VAR/MAX).

The key column looks up from another table so it is static (abc MAX is always 3, def MAX is always 2, so on).

I need to create a new index column based on KEY which restarts to 1 once it reaches its max value.

 KEY MAX INDEX abc 3 1 abc 3 2 def 2 1 def 2 2 abc 3 3 abc 3 1 def 2 1 ghi 4 1 ghi 4 2 ghi 4 3 ghi 4 4 def 2 2

1 ACCEPTED SOLUTION
Super User

you can create an index table in PQ, then use DAX to create a column

``````Column =
if(mod(CALCULATE(COUNTROWS('Table'),'Table'[KEY]=EARLIER('Table'[KEY])&&'Table'[Index]<=EARLIER('Table'[Index])),'Table'[MAX])=0,'Table'[MAX],mod(CALCULATE(COUNTROWS('Table'),'Table'[KEY]=EARLIER('Table'[KEY])&&'Table'[Index]<=EARLIER('Table'[Index])),'Table'[MAX]))``````

pls see the attachment below

Proud to be a Super User!

7 REPLIES 7
Super User

Hi,

How did you arrive at the numbers in the Index column?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

is this a table or a visual?

Proud to be a Super User!

Helper I

Hi! This is in a table

Super User

you can create an index table in PQ, then use DAX to create a column

``````Column =
if(mod(CALCULATE(COUNTROWS('Table'),'Table'[KEY]=EARLIER('Table'[KEY])&&'Table'[Index]<=EARLIER('Table'[Index])),'Table'[MAX])=0,'Table'[MAX],mod(CALCULATE(COUNTROWS('Table'),'Table'[KEY]=EARLIER('Table'[KEY])&&'Table'[Index]<=EARLIER('Table'[Index])),'Table'[MAX]))``````

pls see the attachment below

Proud to be a Super User!

New Member

is this possible in the power query editor? i have the sane structure but in power query instead of columns and measures..

Helper I

Thanks! Works exactly as intended. Just having memory related problems once this is applied to a table with 50k+ rows, so I may have to recreate this in PQ instead of DAX

Appreciate the help!

Super User

I don't know how to do this in PQ, let's see if anyone else can help on M language.

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors