Forum Discussion
ADDCOLUMNS - please explain!
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]
)
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
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
)