topic Re: Aggr function with column return from temp table in DAX Commands and Tips

Aggr function with column return from temp table

Anonymous — Mon, 15 May 2023 02:55:13 GMT

Dear all,

I'm a newbie in DAX, in my lab I try to use SUMX function to sum all premium fee group by industry.

My DAX function is below:

EVALUATE var temp = FILTER( SUMMARIZE( Premium_table, Premium_table[Industry], Premium_table[SUM PREMIUM] ), [SUM PREMIUM] > 10000) RETURN ADDCOLUMNS( temp, "sum_groupby_Industry" , SUMX(temp, temp[SUM PREMIUM]) )

Tried with DAX studio, it failed with log "Query (15, 40) Cannot find table 'temp' , I think I have defined temp table in above already , could you please assist on my issue ?

Re: Aggr function with column return from temp table

Jihwan_Kim — Mon, 15 May 2023 03:05:29 GMT

Hi,

I am not sure if I understood your question correctly, but please try the below whether it provides the desired result.

new addco table = VAR temp = FILTER ( SUMMARIZE ( Premium_table, Premium_table[Industry], Premium_table[SUM PREMIUM] ), [SUM PREMIUM] > 10000 ) RETURN ADDCOLUMNS ( temp, "sum_groupby_Industry", SUMX ( temp, Premium_table[SUM PREMIUM] ) )

Re: Aggr function with column return from temp table

Anonymous — Mon, 15 May 2023 03:24:12 GMT

hi Jihwan_Kim ,

It work, however I'm curious why my code didn't work ? temp table was defined already but dax still said "Cannot find table 'temp'"

Re: Aggr function with column return from temp table

Jihwan_Kim — Mon, 15 May 2023 04:23:59 GMT

Hi,

Thank you for your message.

In my opinion, the below part was the cause, so I changed to the original table name.

temp is a virtual table that is created inside the formula, but when creating it, it used the original column name ( Premium_table[SUM PREMIUM] ) in SUMMARIZE function.