Forum Discussion

myti's avatar
myti
Helper II
9 years ago
Solved

Add calculated index column by DAX

Dear Friends,

 

I have table as a below,I would be happy if you guide me how I can add the calculated index column to the table a New column by a DAX formula. 

 

Thank you,

Myti

 

Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago

    myti

     

    Try to create a calculated column in DAX.

     

    index = 
RANKX (
    FILTER (
        yourTable,
        EARLIER ( yourTable[CC] ) = yourTable[CC]
            && EARLIER ( yourTable[Type] ) = yourTable[Type]
            && yourTable[Cluster] = yourTable[Cluster]
            && EARLIER ( yourTable[Status] ) = yourTable[Status]
    ),
    yourTable[Avg-Position],
    ,
    ASC
)

     

12 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Can you explain the mechanics behind that Index column? If I am reading it correctly, you start at 1 and increment until the "Status" changes and then you start back at 1 and so on.

     

    My feelings are that you would have better success implementing this in "M" rather than DAX.

    • myti's avatar
      myti
      Helper II
      9 years ago

      Thanks Greg_Deckler for your quick reply.

       

      My idea is that to add index based on value of Avg-Position.Changing is not only based on "status".It is based on the  the four first columns.

       

      Thank you

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        myti

         

        Try the following steps

         

        1. Go to the editQuery of the table.

        2. Go to the Add Column Tab

        3. Add Index Column

        4. Rename that column as ColIndex

        5. Close the EditQuery

        6. Right Click Your table

        7. Click on New Column

        8. Paste the following code

            DesiredIndex = CALCULATE(COUNT([ColIndex]), ALL('Table2'),

                                      FILTER('Table2', [ColIndex] <=EARLIER([ColIndex])),

                                     FILTER('Table2', [CC]=EARLIER([CC])),

                                     FILTER('Table2', [Type]=EARLIER([Type])),
                                    FILTER('Table2', [Cluster]=EARLIER([Cluster])),
                                   FILTER('Table2', [Status]=EARLIER([Status]))
                                       )

        You will get the result you want.

         

        If this works for you please accept this as solution and also give KUDOS.

         

        Cheers

         

        CheenuSing

  • omarevp's avatar
    omarevp
    Helper II
    8 years ago

    myti

     

    Hello.

     

    Would you please tell me how you create the column Average Position? i need something like that.

     

    Thanks!

  • ericet's avatar
    ericet
    Helper I
    6 years ago

    Hello everyone,

    I need to add a simple 0 based index to this GENERATESERIES

    Index from 0 to 36.

     

    I need this index to be able to join with another table.

     

    Anyone can help me with this ?

     

    Sample Gen Date Series =
    GENERATESERIES(
    DATE(2020,1,1)
    ,DATE(2023,1,2)
    , 31
    )
     
    Eric (Montreal, Canada)
    • ericet's avatar
      ericet
      Helper I
      6 years ago

      Found the solution, the problem was I'm generating a dynamic table with GENERATESERIES but could not join it with another table in my model. I needed a common column to be able to join.
      The solution is RANKX, since these are dates and they are in the proper order I added a column with RANKX.

      New column
      Sample Gen Date Series =
      GENERATESERIES(
      DATE(2020,1,1)
      ,DATE(2023,2,2)
      , 31)

      Then RANKX to generate the index,
      Index = RANKX( ALL('Sample Gen Date Series'),'Sample Gen Date Series'[Value].[Date],,ASC)
      because I needed a base 0 index I made a New Column = 'Sample Gen Date Series'[Index] - 1

      That's it
      Eric

  • hnblann's avatar
    hnblann
    New Member
    3 years ago

    I'm trying to do something similar, but instead of "status", I have a date column and I want the index to calculate based on ascending date.  How do I add that step? Thanks