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

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
      )