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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MasterP
Helper I
Helper I

Peculiar behavior of <name> argument in Summarize

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.
2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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

Best
D

View solution in original post

Anonymous
Not applicable

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

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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

Best
D

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
Not applicable

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

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

Pragati11
Super User
Super User

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors