ADDCOLUMNS - please explain!

[igaca]

Greetings folks!

I have a calculated table which contains two columns with date values.  Now I wish to generate another column which calculates their difference.

I am utilizing ADDCOLUMNS to do this but am not able to get the correct results no matter the approach I've taken thus far.  It seems that no matter then approach, I am unable to get the proper context transition to rows of the generated table (in this example I am passing it on to ADDCOLUMNS in form of a variable)

Can someone savvy explain to me what would work in this instance and -more importantly- why?  Thank you!

[jeffrey_wang]

A couple of suggestions:

(1) A perf suggestion, change the pattern from FILTER(GENERATE(e1, e2), p) to GENERATE(e1, FILTER(e2, p)).

(2) DAX variables are evaluated at the place of definition, not at the place of invocation. As a result, CALCULATE(variable, ...) never changes the content of the variable. For the CALCULATE function to work as you want, we need a new feature called table valued measure which does not exist yet.

(3) Because of (2), you have to use FILTER function instead of CALCULATE function. Change the CALCULATE function to

COUNTROWS(

  FILTER(

    InputTable,

    [Date] >= EARLIER([Work Start]) && [Date] <= EARLIER([Work Finish]

)

[igaca]

Jeffrey,  thanks for the input!

1) Understood. Filter the second table such that GENERATE has less-taxing of a cross join operation to perform.

2) I think I understand what you're after but let's confirm:  The <filter> argument of CALCULATE will not modify the <expression> argument as that expression -being a variable-  "is a rigid thing" ... has already been defined elsewhere?  In order to do so, we need to iterate over the variable defined table directly hence FILTER(InputTable,...) and then simply wrap that with a COUNTROWS to return the correct, desired result?

The reason why I used CALCULATE in the example is that I was under the impression -perhaps a false one- that CALCULATE was necessary to transition the ADDCOLUMNS <expression> argument context as it iterates over it's <table> argument?

ADDCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>]…)  

below are a few approaches and associated results.  Please take a look then help straighten me out on what exactly is taking place and why.

Approach V2 - ResultApproach V3 - ResultApproach V4 - Result

[jeffrey_wang]

1. After you move the FILTER from outside GENERATE to inside GENERATE, you need to remove the ouside FILTER.

2. CALCULATE converts the current row of ADDCOLUMNS into filters and then evulate the first argument. But since the first argument of CALCULATE is a variable which has already been evaluated in the context of the definition, the newly converted filters have no effect.

3. There seems to be a product bug in using EARLIER function and variable together. Change the expression of "Work Duration" to the following instead:

VAR vWorkStart = [Work Start]

VAR vWorkFinish = [Work Finish]

RETURN

COUNTROWS(

  FILTER(

    InputTable,

    [Date] >= vWorkStart && [Date] <= vWorkFinish

)

[Phil_Seamark]

HI @igaca

Rather than try and put the logic into your ADDCOLUMNS statement at the point you create the calculated table, why not just add a calculated column to the table once you have created it with the same logic?

[igaca]

I already addressed the issue via a calculated column, am just trying to understand why no context transition is taking place (notice the value returned for each row is that of the entire table) to the ADDCOLUMNS (which is an iterator).

My guess is that this has something to do with the fact InputTable variable is a virtual table and does not physically map to the query-derived tables in the model but would love for someone who knows to explain what is actually going on.

Thanks for your input Phil.

[Phil_Seamark]

Oh and further to your question as to why.

I believe the formula you are using relies on a phyiscial table, and you are adding the code part way through the creation of table so it's not yet a phyisical table.