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 prov...
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

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