Forum Discussion

maracles's avatar
maracles
Resolver II
10 years ago

Self Referencing CALCULATEDTABLE()

I have the following calcualted table:

Customer First Medium = 
	SELECTCOLUMNS( 
		CALCULATETABLE(
			'Opportunity Description',
			'Opportunity Description'[Found in GA] = "Found"
		),
		"Order Number",[Order Number],
		"Email", [Email],
		"Medium Friendly", [Medium Group]	
	)

However I want to add an addition column using ADDCOLUMNS() but I want it to be based on the calculated table created above. Is this possible? The first parameter for ADDCOLUMNS() is a table name, but giving it the table name of "Customer First Medium" does not do anything. 

To give a usecase. The calcualted table creates a filtered table from my main table "Opportunity Description". I know want to rank this filtered table by adding the following ranking column:

RANKX(
    FILTER(
         Customer First Medium,
         [Email]=EARLIER(Customer First Medium[Email])),
         [Order Number],
         [Order Number],1
         ,DENSE)


How would I do this? Thanks. 

 

Need Help

1 Reply

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    10 years ago

    Hi maracles

     

    According to your description, you want to add a rank column into your calculated table. Right?

     

    In DAX, ADDCOLUMNS() function will return a new table contains original column and add columns. This is based on a created table. But we can't generate this calculated table with rank column through one DAX, it's not possible to achieve this "Self Referencin​g". Because when revolving the RANKX() function, the table is not created completely yet, however this rank column is also a part of table, it is running into paradox. So for your requirement, since you have generated the calcualted table first, you can do modeling on your dataset and add calculated column with RANKX() function populated.