Forum Discussion

myti's avatar
myti
Icon for Helper II rankHelper 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
)

     

EduSurveys's avatar
EduSurveys
Icon for Advocate II rankAdvocate 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
Icon for Advocate II rankAdvocate 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
)