Forum Discussion

matthewchilton's avatar
matthewchilton
Advocate I
8 years ago
Solved

Summarzing a table variable?

I have a table value in a variable and would like to summarize it, grouping some columns and summing others. Does anyone know a nice way to achieve it?   Here's what I've tried so far.   I can...
  • v-sihou-msft's avatar
    v-sihou-msft
    8 years ago

    matthewchilton

     

    In DAX, it can't determine the current context since the row context is not generated yet when resolving the expression. You have to use a calculated column formula to calculate the results.

     

    For your requirement, I think the easiest way is creating a calculated table instead of table variable. Then summarize this calculated table.

     

    Regards,

  • matthewchilton's avatar
    matthewchilton
    8 years ago

    fyi - if anyone is looking at this facing the same challenge, it can be solved easily using GROUPBY and CURRENTGROUP().

     

     

    EVALUATE
VAR cars = DATATABLE(
	"Maker", STRING, "Sales", INTEGER, {
		{ "Ford", 300 },
		{ "Jaguar", 180 },
		{ "Jaguar", 1 }
	})
	
RETURN
GROUPBY(
	cars,
	[Maker],
	"total sales",
	SUMX(CURRENTGROUP(),[Sales])
)

     

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
8 years ago

matthewchilton

 

In DAX, it can't determine the current context since the row context is not generated yet when resolving the expression. You have to use a calculated column formula to calculate the results.

 

For your requirement, I think the easiest way is creating a calculated table instead of table variable. Then summarize this calculated table.

 

Regards,

matthewchilton's avatar
matthewchilton
Advocate I
8 years ago

Thanks very much for the reply.  Sounds like a sensible suggestion.   Awkwardly for me, we are allowing the analyst to enter some parameters on the fly for the query, and so I'm not able to pre-compute the result in calculted columns or a calculated table.

I was really intrigued by your comment that "the row context is not generated yet when resolving the expression".  I've read loads about DAX and done lots of experimentation, but hadn't come across this knowledge before.  Is there a book or some kind of online resource I can consult for learning DAX at this level of detail...?

 

Thanks again!

Matt.