In DAX, how do I RETURN the sum of a calculated column from a DAX Table Variable (created via ADDCOLUMN)?

2
votes

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

1
powerbidaxssas-tabular

1 Answers

1
votes

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