cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
myti Regular Visitor
Regular Visitor

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

 

Capture1.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: Add calculated index column by DAX

@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
)

 

Capture.PNG

8 REPLIES 8
Super User
Super User

Re: Add calculated index column by DAX

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


myti Regular Visitor
Regular Visitor

Re: Add calculated index column by DAX

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 youCapture1.JPG

Highlighted
Super User
Super User

Re: Add calculated index column by DAX

@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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
myti Regular Visitor
Regular Visitor

Re: Add calculated index column by DAX

@CheenuSing

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

Moderator Eric_Zhang
Moderator

Re: Add calculated index column by DAX

@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
)

 

Capture.PNG

PowerQueryFTW Frequent Visitor
Frequent Visitor

Re: Add calculated index column by DAX

Thanks so much!

omarevp Regular Visitor
Regular Visitor

iyRe: Add calculated index column by DAX

@myti

 

Hello.

 

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

 

Thanks!

EduSurveys Frequent Visitor
Frequent Visitor

Re: Add calculated index column by DAX

@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.