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
)

     

ericet's avatar
ericet
Helper I
6 years ago

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's avatar
ericet
Helper I
6 years ago

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