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
)

     

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

  • myti's avatar
    myti
    Helper II
    9 years ago

    Anonymous

    Thanks to you for your solution.

    The problem is that the table I have is a summerised table that were creatd by DAX formulah in data view section.SO I dont have access to my table in query editor.

    As Greg_Deckler advised ,I am thinking to recreate the the tables that I created in Data view in query editor section by M language.

     

    Thank you,

    Regards

    Medi

    • Eric_Zhang's avatar
      Eric_Zhang
      Microsoft Employee
      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
)

       

      • PowerQueryFTW's avatar
        PowerQueryFTW
        Frequent Visitor
        8 years ago

        Thanks so much!