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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
chefe
Helper II
Helper II

PERCENTILE inside CALCULATE does not (anymore) recognise data fields

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.

 

pct_value.png

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

pct_nont_found.png

 

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

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

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 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thank you Owen! That seems to work jusst fine 🙂

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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