Forum Discussion

lbendlin's avatar
lbendlin
Super User
4 years ago

Can I use a table variable inside a table variable?

This has been bugging me for a while.

 

Why can I do this:

 

var wd = SELECTCOLUMNS(Filter(Dates,Dates[StartOfMonth]=Sick[Month] && Dates[IsWeekDay]=TRUE()),"Date",Dates[Date])
var ab= SUMMARIZE(Filter(Absence,Absence[LongTerm]=Sick[LongTerm]),Absence[Personnel Number],Absence[Start Date],Absence[Actual End Date])
var ab1=ADDCOLUMNS(ab,"d",COUNTROWS(INTERSECT(CALENDAR(Absence[Start Date],Absence[Actual End Date]),wd)))

 

 

but not this:

 

var wd = SELECTCOLUMNS(Filter(Dates,Dates[StartOfMonth]=Sick[Month] && Dates[IsWeekDay]=TRUE()),"Date",Dates[Date])
var ab= SUMMARIZE(Filter(Absence,Absence[LongTerm]=Sick[LongTerm]),Absence[Personnel Number],Absence[Start Date],Absence[Actual End Date],"Cal",CALENDAR(Absence[Start Date],Absence[Actual End Date]))
var ab1=ADDCOLUMNS(ab,"d",COUNTROWS(INTERSECT([Cal],wd)))

 

 

or more generic

 

var tablevariable1 = SUMMARIZE(table,column1,column2,"new column",tablevariable2)
var tablevariable3 = ADDCOLUMNS(tablevariable1,"Count",COUNTROWS([tablevariable2]))

 

 

i need to walk up and down a multidimensional hierarchy and do aggregrations between the hierarchies. That requires temporarily storing tables in fields of the parent table, and then retrieving the child tables again for processing.

 

So far the only option I can see is to do a fully explicit table processing without the possibility of storing the table variable for later retrieval.  Is that a DAX design limitation or am I using the wrong syntax? This is very easy to do in Power Query, so I hope that I'm just overlooking something obvious.

1 Reply

  • Stachu's avatar
    Stachu
    Community Champion
    4 years ago

    you can use the table variables within the table expression, but the syntax has to be valid, which in your case is not possible with SUMMARIZE

    the issue is here:

    VAR ab =
    SUMMARIZE (
        FILTER ( Absence, Absence[LongTerm] = Sick[LongTerm] ),
        Absence[Personnel Number],
        Absence[Start Date],
        Absence[Actual End Date],
        "Cal", CALENDAR ( Absence[Start Date], Absence[Actual End Date] )
    )

    SUMMARIZE requires last argument to be scalar expression, CALENDAR returns table, which makes the syntax invalid and causes error.

     

    the reason it works for

    VAR ab =
    SUMMARIZE (
        FILTER ( Absence, Absence[LongTerm] = Sick[LongTerm] ),
        Absence[Personnel Number],
        Absence[Start Date],
        Absence[Actual End Date]
    )
VAR ab1 =
    ADDCOLUMNS (
        ab,
        "d",
            COUNTROWS (
                INTERSECT ( CALENDAR ( Absence[Start Date], Absence[Actual End Date] ), wd )
            )
    )

    is that the result of CALENDAR is only evaluated in memory when caclulating the scalar value - no need for "materialized" nested tables