Summarize virtual table

[pkoetzing]

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?

[jdbuchanan71]

Perhaps these will help.

https://www.sqlbi.com/articles/understanding-context-transition-in-dax/

https://www.youtube.com/watch?v=IeaPNw_YuQc&t=78s&ab_channel=SQLBI

[jdbuchanan71]

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] )

[pkoetzing]

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

[mangaus1111]

Hi @pkoetzing ,

do you have any sample data and your expected result?

[pkoetzing]

My question is rather: Why can I access a [column] of a table variable, but only use it in SUMX() not SUM()?

[pkoetzing]

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. 

[jdbuchanan71]

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] ) )
)