topic Re: Use of CALENDAR() as a table variable in DAX Commands and Tips

Use of CALENDAR() as a table variable

lbendlin — Fri, 18 Sep 2020 15:30:06 GMT

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

Re: Use of CALENDAR() as a table variable

Greg_Deckler — Fri, 18 Sep 2020 16:24:07 GMT

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/ba-p/1248635

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

Re: Use of CALENDAR() as a table variable

lbendlin — Fri, 18 Sep 2020 16:28:38 GMT

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

Re: Use of CALENDAR() as a table variable

Greg_Deckler — Fri, 18 Sep 2020 16:39:23 GMT

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!

Re: Use of CALENDAR() as a table variable

lbendlin — Fri, 18 Sep 2020 17:07:31 GMT

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.

Re: Use of CALENDAR() as a table variable

Greg_Deckler — Fri, 18 Sep 2020 18:00:57 GMT

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.

Re: Use of CALENDAR() as a table variable

Fowmy — Fri, 18 Sep 2020 20:37:51 GMT

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

Re: Use of CALENDAR() as a table variable

lbendlin — Fri, 18 Sep 2020 20:48:56 GMT

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.