PERCENTILE inside CALCULATE does not (anymore) recognise data fields

[chefe]

Hi there,

I recently figured out that I can write an in-memory table in DAX. I just set-up a table that spans from 1-100, and I want to add a column returning the percentile for 1-100 for a given column of another table (exchange rates).

Now I successfully produced this using the following code.

Table = 

VAR dat = DATE(2016;1;1)

VAR pct = SUMMARIZE(
	ADDCOLUMNS(
		CALENDAR(
			dat
			;dat+99
		)
		;"pct"
		;[Date]-dat+1
	)
	;[pct]
)

VAR pct_value = ADDCOLUMNS(
	pct
	;"pct_value"
	;PERCENTILE.INC(
		'Foreign exchange rates'[INDIRECT_FX_CCY_QUOTE]
		;[pct]/100
	)
)


RETURN pct_value

So I create this table with two columns (pct 1-100 on x-axis, pct_value as a percentile function of "pct" on y-axis).

The code reads as follows:

dat = fixed date

pct = creating a one-column calculated table from 1-100 by using a dirty workaround using the calendar function

pct_value = adding one column to the pct table which should return the 1,2,3,...,100 th percentile of the field 'Foreign exchange rates'[INDIRECT_FX_CCY_QUOTE].

The result is valid, I double checked with excel, BUT, since the table 'Foreign exchange rates' houses several currencies, the percentile is calculated over the whole population and therefore is meaningless.

Since the calculated table is not related to the data model I cannot use any filters on the chart above. I therefore tried modifying the filter context using CALCULATE function to only those records in 'Foreign exchange rates' where [FX_CCY]="USD".

Table = 
VAR dat = DATE(2016;1;1)
VAR pct = SUMMARIZE(
	ADDCOLUMNS(
		CALENDAR(
			dat
			;dat+99
		)
		;"pct"
		;[Date]-dat+1
	)
	;[pct]
)

VAR pct_value_calculate = ADDCOLUMNS(
	pct
	;"pct_value"
	;CALCULATE(
		PERCENTILE.INC(
			'Foreign exchange rates'[INDIRECT_FX_CCY_QUOTE]
			;[pct]/100
		)
		;'Foreign exchange rates'[FX_CCY]="USD"
	)
)

RETURN pct_value_calculate

Unfortunately this does not work. It appears that when putting the PERCENTILE function inside a CALCULATE function, it no longer recognises the [pct] field on my calculated table. 

Has somebody a clue on how I can solve this problem? Or, if there is a totally different approach that is better suited to my scenario, I am all ears ;D

Looking forward to your feedback, chefe

[OwenAuger · Accepted solution]

Hi Chefe,

I think your best bet is to create a variable to store the value of the current row's [pct] before calling CALCULATE:

Table =
VAR dat =
    DATE ( 2016; 1; 1 )
VAR pct =
    SUMMARIZE (
        ADDCOLUMNS ( CALENDAR ( dat; dat + 99 ); "pct"; [Date] - dat + 1 );
        [pct]
    )
VAR pct_value_calculate =
    ADDCOLUMNS (
        pct;
        "pct_value";
        VAR CurrentPct = [pct]
        RETURN
            CALCULATE (
                PERCENTILE.INC (
                    'Foreign exchange rates'[INDIRECT_FX_CCY_QUOTE];
                    CurrentPct / 100
                );
                'Foreign exchange rates'[FX_CCY] = "USD"
            )
    )
RETURN
    pct_value_calculate

I think a rough explanation is that when CALCULATE triggers a context transition (i.e. row context becomes filter context), any columns within the row context that were created with ADDCOLUMNS (like [pct]) are no longer accessible within the first argument of CALCULATE. This is because 'created columns' have no lineage so cannot be converted to filter context.

This means you can refer to [pct] outside CALCULATE or in one of CALCULATE's filter arguments, but not in CALCULATE's first argument. So the only way I can think of to access the value of [pct] is to save it in a variable before calling CALCULATE.

Owen 🙂

[chefe]

Thank you Owen! That seems to work jusst fine 🙂