Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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.

 

KEYMAXINDEX
abc31
abc32
def21
def22
abc33
abc31
def21
ghi41
ghi42
ghi43
ghi44
def22

 

Thanks in advance!

Need Help
Tips and Tricks
  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    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

8 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi! This is in a table

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        3 years ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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