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= SUMMA...
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