topic Peculiar behavior of <name> argument in Summarize in DAX Commands and Tips

Peculiar behavior of argument in Summarize

MasterP — Fri, 22 May 2020 15:06:47 GMT

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.

Re: Peculiar behavior of argument in Summarize

Pragati11 — Fri, 22 May 2020 16:56:40 GMT

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

Re: Peculiar behavior of argument in Summarize

Anonymous — Sat, 23 May 2020 00:29:58 GMT

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

Best
D

Re: Peculiar behavior of argument in Summarize

MasterP — Sat, 23 May 2020 06:39:34 GMT

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.

Re: Peculiar behavior of argument in Summarize

Anonymous — Sat, 23 May 2020 08:33:13 GMT

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

Re: Peculiar behavior of argument in Summarize

MasterP — Sat, 23 May 2020 16:13:09 GMT

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