cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
myti
Helper II
Helper II

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

@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

View solution in original post

11 REPLIES 11
ericet
Frequent Visitor

Hello everyone,

I need to add a simple 0 based index to this GENERATESERIES

Index from 0 to 36.

 

I need this index to be able to join with another table.

 

Anyone can help me with this ?

 

Sample Gen Date Series =
GENERATESERIES(
DATE(2020,1,1)
,DATE(2023,1,2)
, 31
)
 
Eric (Montreal, Canada)
ericet
Frequent Visitor

Found the solution, the problem was I'm generating a dynamic table with GENERATESERIES but could not join it with another table in my model. I needed a common column to be able to join.
The solution is RANKX, since these are dates and they are in the proper order I added a column with RANKX.

New column
Sample Gen Date Series =
GENERATESERIES(
DATE(2020,1,1)
,DATE(2023,2,2)
, 31)

Then RANKX to generate the index,
Index = RANKX( ALL('Sample Gen Date Series'),'Sample Gen Date Series'[Value].[Date],,ASC)
because I needed a base 0 index I made a New Column = 'Sample Gen Date Series'[Index] - 1

That's it
Eric

omarevp
Helper II
Helper II

@myti

 

Hello.

 

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

 

Thanks!

Greg_Deckler
Super User IV
Super User IV

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




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

CheenuSing
Community Champion
Community Champion

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

@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

@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

View solution in original post

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

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
)

Thanks so much!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors