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
)

     

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!

    • EduSurveys's avatar
      EduSurveys
      Advocate II
      7 years ago

      Eric_Zhang 

      How would you do this if you wanted to do a calculated index for "status" in chronological order? So, if there were dates associated with the different rows, Index = 1 for the earliest date of Low status and = 3 for the latest date of Low status.

      • EduSurveys's avatar
        EduSurveys
        Advocate II
        7 years ago

        I figured out my solution:

         

        FactEnrollment is my table of enrollment snapshots every quarter.
        Term is the name of the quarter.
        Each student has a row for every term they exist, and this will rank each student ID's entries (whether they have 10 rows or 6 rows or 3 rows) in chronological order by term.
         
        EnrollIndex =
        RANKX(
        FILTER(
        'FactEnrollment',
        EARLIER('FactEnrollment'[ID]) = 'FactEnrollment'[ID]),
        'FactEnrollment'[TERM],
        ,
        ASC
        )