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

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
3 years ago

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's avatar
    ryan_mayu
    Super User
    3 years ago

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