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 

is this a table or a visual?

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

    • k_rojvanakarn's avatar
      k_rojvanakarn
      New Member
      3 years ago

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