Peculiar behavior of <name> argument in Summarize

[MasterP]

The following works to create a table: 

ReturnsbyMonth =

SUMMARIZE (

Returns,

Dates[YearMonth],

"AA", SUMX ( FILTER ( Returns, Returns[Vehicle] = 1 ), Returns[Return] )

)

The following also works:

ReturnsbyMonth =

VAR

C1 = "AA"

RETURN

SUMMARIZE (

Returns,

Dates[YearMonth],

C1, SUMX ( FILTER ( Returns, Returns[Vehicle] = 1 ), Returns[Return] )

)

However, the following fails with the error "Function SUMMARIZE expects a column name as argument number 3" 

ReturnsbyMonth =

VAR

C1 = LOOKUPVALUE(Vehicles[Ticker], Vehicles[Vehicle], 1)

RETURN

SUMMARIZE (

Returns,

Dates[YearMonth],

C1, SUMX ( FILTER ( Returns, Returns[Vehicle] = 1 ), Returns[Return] )

)

I have confirmed that the value returned by LOOKUPVALUE(Vehicles[Ticker], Vehicles[Vehicle], 1) is of type STRING. There must be some subtlety about the return type of the LOOKUPVALUE function that I do not understand (it claims to return a scalar), or about the expectations of the SUMMARIZE function Any thoughts?

Basically I am trying to build a summary that will respond dynamically to the data rather than have a fixed column name.

[Anonymous · Accepted solution]

You CANNOT use dynamically calculated strings as the names of columns. Easy as that.

Best
D

[Anonymous · Accepted solution]

You CANNOT use dynamically calculated strings as the names of columns. Easy as that.

Best
D

[MasterP]

Ok fine, I guess that sorts it. But how does SUMMARIZE know the column name is dynamically calculated,? I thought the whole point of VARs is that they are calculated before the actual code.

[Anonymous · Accepted solution]

Hi there.

DAX knows EVERYTHING about itself. In the first case you could use a VAR as the name because you set it to a constant that would never change during the lifetime of a session. I the second case you wanted to use a function that would possibly return different values during a session. DAX, of course, knows this.

Secondly, please read this:

https://dax.guide/summarize/

and stop using SUMMARIZE to calculate anything under it. You'll notice in there that some of its arguments are DEPRECATED AND NOT RECOMMENDED. If you want to see why this is so, please read this:

https://www.sqlbi.com/articles/all-the-secrets-of-summarize/

Best
D

[MasterP]

Thanks D, that was all totally on point. Much appreciated.

[Pragati11]

Hi @MasterP ,

In the first 2 cases you are actually passing a single value in SUMMARIZE function - "AA" and C1 = "AA"

But, in 3rd case, LOOKPVALUE function returns multiple values in column Vehicles[Ticker] where you get a match.

Therefore, it's giving error.

Also, if you refer the foloiwng link on LOOKUPVALUE and check "Returns Value" section:

https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati