Forum Discussion
Summarize virtual table
I have a calculated table
Combine work per date =
UNION(
SELECTCOLUMNS(
'Work',
"Date', <expression>,
"Minutes", <expression>
),
SELECTCOLUMNS(
'Work',
"Date', <expression>,
"Minutes", <expression>
)
)that I want to aggregate with SUMMARIZE()
Aggregate work per date =
VAR tbl = 'Combine work per date'
RETURN
SUMMARIZE(
tbl,
[Date],
"Minutes", SUM([Minutes])
)Referencing the [Date] column is no problem, but referencing [Minutes] in the SUM() aggregation leads to an
Cannot identify the table that contains [Minutes] column. error.
How can I work around this?
7 Replies
You should be able to do it with a SUMX like this.
Aggregate work per date = SUMX ( VALUES ( 'Combine work per date'[Date] ), CALCULATE ( SUM ( 'Combine work per date'[Minutes] ) ) )Thanks for the hint with SUMX, but in a measure I could even use
Aggregate work per date = VAR tbl = 'Combine work per date' RETURN SUMX ( tbl, [Minutes] )which includes a table variable.
My question is rather: Why can I access a [column] of a table variable, but only use it in SUMX() not SUM()?
Bucause SUMX is an iterator function.
When you are writing measure should should always include the table name when referencing a column and never include the table name when referencing a measure. It you measure it looks like [Minutes] is a measure.
Aggregate work per date = VAR tbl = 'Combine work per date' RETURN SUMX ( tbl, [Minutes] )Also, either of these measure should return the same data as the first one.
Aggregate work per date = SUMX ( 'Combine work per date', 'Combine work per date'[Minutes] )Aggregate work per date = SUM ( 'Combine work per date'[Minutes] )You are totally wrong.
- tbl is not a table of the datamodel, but a variable inside a DAX expression
- [minutes] is a valid reference to a column of that variable, and not a measure
- tbl[minutes] or even 'tbl'[minutes] are not a valid DAX expression
- I'm aware that your expressions work fine with tables of the data model, but the question is why they don't work with tables stored in variables (which are actually constants)
- SUMX is an iterator and SUM is an aggregator, but why does SUMX accept the [minutes] column reference, while SUM doesn't?
- I was hoping someone could explain how this relates to the DAX data lineage
