Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

12 REPLIES 12
hnblann
New Member

I'm trying to do something similar, but instead of "status", I have a date column and I want the index to calculate based on ascending date.  How do I add that step? Thanks

ericet
Helper I
Helper I

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)

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
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.