Use of CALENDAR() as a table variable

[lbendlin]

Here's the scenario:  Given a row in a table ("Intervention) with a set of two datetime columns ("Start" and "End")  I want to compute the difference between the two timestamps excluding certain days in between (let's say saturday and sunday).

First approach would be to create a table variable with a calendar of these two dates

Difference =

var db = CALENDAR(Intervention[Start],Intervention[End])

That seems to work as I get a proper row count from that when using test timestamps - it is including both the start and end days.

According to the documentation the above should result in a single column table with a column "Date".

https://docs.microsoft.com/en-us/dax/calendar-function-dax

However, any subsequent attempts to use that table (apart from a standalone COUNTROWS)  fails.

return COUNTROWS(db)  works but for example 

var we = CALCULATE(COUNTROWS(db),WEEKDAY(db[Date],2)>5)

fails to find the table 'db'. Any idea what might be going on here?

Update: ADDCOLUMNS() works. But why?

var we = ADDCOLUMNS(CALENDAR(Intervention[Start],Intervention[End]),"WE",if(WEEKDAY([Date],2)>5,1,0))

[Fowmy]

@lbendlin 

Agree with @Greg_Deckler's points and the explanations.

@lbendlin 

So in your case below approach should working, interested to know how you approached it.

Difference = 
var db = CALENDAR(Intervention[Start],Intervention[End])
return
SUMX(db,IF( WEEKDAY([Date],2) > 5, 1,0))

Notice the RED highlighting, it's still valid though.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[lbendlin]

@Fowmy 

Yes, that was one of the options I eventually presented to the OP

Difference = DATEDIFF(Intervention[Start],Intervention[End],MINUTE)-1440*sumx(CALENDAR(Intervention[Start],Intervention[End]),if(WEEKDAY([Date],2)>5,1,0))

I first tried to use COUNTAX  but that doesn't seem to work well with binary values

Difference = DATEDIFF(Intervention[Start],Intervention[End],MINUTE)-1440*COUNTAX(CALENDAR(Intervention[Start],Intervention[End]),WEEKDAY([Date],2)>5)

won't actually give the expected result.

[Greg_Deckler · Accepted solution]

@lbendlin You can't use CALCULATE with virtual tables. It is the main reason I do not use CALCULATE very often:

https://community.powerbi.com/t5/Community-Blog/CALCUHATE-Why-I-Don-t-Use-DAX-s-CALCULATE-Function/b...

You will want to use SUMX, AVERAGEX, MAXX, etc.

And, for what you are originally doing, you could just use ([End] - [Start]) * 1. or DATEDIFF

[lbendlin]

Thank you. As i mentioned in my update ADDCOLUMNS works too, even though the DAX intellisense complains passive-aggressively.

[Greg_Deckler]

@lbendlin Yeah, if you look at most of my DAX in things like the Quick Measures Gallery, I use tons of virtual tables. Everything in DAX pretty much works with virtual tables except CALCULATE and a few other functions that require physical tables like MAX, MIN, etc. so you have to use the equivalents MAXX, MINX, etc. But yeah, ADDCOLUMNS works great, I use ADDCOLUMNS with virtual tables a ton!

[lbendlin]

I think the iffy part is that you need to know the column names that some of these virtual tables generate. Sometimes it seems to be [Value] but in the CALENDAR() example it is [Date]  etc.  Would be nice if DAX could address table columns by number.

[Greg_Deckler]

@lbendlin Yeah, it's always Value except for the Calendar. Intellisense/type ahead does work for these virtual tables. If I am not mistaken I believe: { (4, 5, 6), (7, 8, 9) } creaes Value1, Value2, Value3 columns but if just a single column gets created it is always Value unless it is from a Calendar function. If you create a table from a physical table, 

VAR __Table = 'Table'

The names of the columns are the same as in the physical table. For most other ways of creating virtual tables or adding columns you explicitly name them.