Conditional Index with Maximum Value

[Anonymous]

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

Thanks in advance!

[MyroslavaM]

Hi @k_rojvanakarn , @ryan_mayu , @Anonymous 

Might be useful for you.

In this case, I generate values from 1 to 5 and repeting them based on Index column.

= List.Accumulate(#"Added Index"[Index],{0}, (state,current) =>  
    if current = 0 then {1} 
    else if List.Last(state)>4 then
    state &{List.Last(state)-4} else state & {List.Last(state)+1})

Here is a good video related how to use other approach https://youtu.be/Hc3d8rMSXcQ?si=S1Adls_HbrgPn4bw

[Ashish_Mathur]

Hi,

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

[ryan_mayu]

@Anonymous 

is this a table or a visual?

[Anonymous]

Hi! This is in a table

[ryan_mayu · Accepted solution]

@Anonymous 

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

[k_rojvanakarn]

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

[Anonymous]

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!

[ryan_mayu]

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