3

I have a multi-step process that stages table variables summarizing base tables to matching grains.

The Process (in a nutshell):

  • removes the selected date filter
  • creates a filter using calculated values
  • the new filtered tables then get summarized to make the grains match
  • resultant tables are joined to form a coverage table
  • to that, I use ADDCOLUMNS to create a calculated column
  • in that, a calculation uses existing columns, a scalar variable, plus a scalar cost (via LOOKUP)

In the end, I have a table with a new column that contains the value I need to RETURN. I am overjoyed by the fact I can copy and paste the table contents in Excel and validate the data. It produces the correct result.

...However, given that the value is contained in a new column in a table variable, it has no equivalent in a base table, and I'm stuck. How do I RETURN the value of this table variable column?

(NOTE: DAX does allow me to use SUMX just to sum the table variable column into a new variable and return that variable, but SUMX forwards the original row context with it and then the result is filtered to the originally selected period which overrides my calculated date range. This does allows me to "EVALUATE" the SUM in DAX Studio, but when I bring the code into Tabular and use it, I get a filtered result.)

Chris Adragna
  • 625
  • 8
  • 18

1 Answers1

3

You can access column variables of previously defined table variables by SUMX function. 

Measure = 
var DaxTable = ADDCOLUMNS(...)

var result = 
CALCULATE(
    SUMX( DaxTable , [amount])
)
return
    result
Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
  • Thank you. I appreciate your answer. I did figure this out already, and I referred to this in my "Note" in the question. When I use SUMX the value returned retains row context and then the original filter (period) in the presentation layer filters (despite my building of a table variable removing period filter, and also wrapping SUMX with a CALCULATE (all of the column). I can't seem to escape the filter! :-) – Chris Adragna Nov 26 '19 at 18:01
  • 1
    Your question was a pleasant puzzle for me. I had not known the answer right away. Parallel I was struggling with similar issue https://stackoverflow.com/a/59052424/1903793 Your problem of using table variable turned out to be a milestone to solve my problem. Thank you for the riddle and thank you for accepting the answer. If you still have further concerns, please share sample pbix file. – Przemyslaw Remin Nov 26 '19 at 20:02
  • It is my pleasure. I came back to say that I was wrong about how my user selected filter was "returning." It turns out that my very first base table, where I thought I was removing the selected period, I was not. I was actually starting out with a filtered set. Thank you again. I plan to ask an entirely new, specific question regarding the filter removal. – Chris Adragna Nov 26 '19 at 23:59