Rank column directly in table creation script?

[sersanch]

Hi guys,

I'm trying to get a rank column in the same script I create a new table as follows, but reference inside filter to tmp1 is not accepted (tmp1 table not found). If I just return the table without the rank, and then add a new calculated column with the code below, it works fine. My question is, if there's a way to directly create the rank in the same script as table. Thanks to all!!

---------not working code--------

aidsByDateCarts =

   var tmp1 = FILTER(

                SUMMARIZE(train,

                          train[events.type],

                          train[timest].[Date],

                          train[events.aid],

                          "numEvents", COUNT(train[events.aid])),

                train[events.type]="carts")

    var tmp2 = ADDCOLUMNS(tmp1, "rank", RANKX(FILTER(tmp1, tmp1[Date]=EARLIER(tmp1[Date])),

                                                                                    [numEvents]))

    return tmp2

------- if I create later calculated column based on new table it works ok---------

RankByDate =

RANKX (

    FILTER(aidsByDateCarts, aidsByDateCarts[Date]=EARLIER(aidsByDateCarts[Date])),

    aidsByDateCarts[numEvents])

[tamerj1 · Accepted solution]

Hi @sersanch 

You cannot use a variable name as a table reference. You can try by referring the column name only

aidsByDateCarts =
VAR tmp1 =
FILTER (
SUMMARIZE (
train,
train[events.type],
train[timest].[Date],
train[events.aid],
"numEvents", COUNT ( train[events.aid] )
),
train[events.type] = "carts"
)
VAR tmp2 =
ADDCOLUMNS (
tmp1,
"rank", RANKX ( FILTER ( tmp1, [Date] = EARLIER ( [Date] ) ), [numEvents] )
)
RETURN
tmp2