Forum Discussion

StSupQ's avatar
StSupQ
Frequent Visitor
3 years ago
Solved

How to remove context from a temporary contextually-calculated table-variable ?

Hello all,

I am currently working on a moving average measure.
The underlying data i want to smooth that way is already a measure. I am already displaying it on a visual as a fonction of a [Time] column, and i want to display the smoothed curve on the same visual. My visual will be filtered on another [Perimeter] column, that is why the underlying data is already a measure.

I have created a temporary table with the ADDCOLUMNS function as follows: 

VAR tempTable=
ADDCOLUMNS(
    FILTER(
        'Table1',
        [Time]>=windowStart && [Time]<= windowEnd && [Perimeter]=perim
    ),
    "Measure Value",
    [Measure]
)


I want to return the average value of the column [Measure Value] from that table.
I can't just use AVERAGEX as it keeps the underlying filter context, and I can't use ALL, ALLSELECTED or REMOVEFILTERS as it will display as an error that i must reference a table and not use the expression of one. Since this temporary table is contextually created, I can't just use a fixed table which would be easy. I would love to just use AVERAGE(tempTable[MeasureValue]) but it seems like you can't reference columns from table variables that way.

Any help of any kind would be greatly appreciated.
Thank you, 
StSupQ

Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    Within the definition of the column you're adding using ADDCOLUMNS you can manipulate the filter context in any way you want, or you can manipulate the filter context in a CALCULATETABLE as in the above example where the date is being manipulated.

8 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    You can use AVERAGEX over the temporary table, 

    Avg of measure =
VAR tempTable =
    ADDCOLUMNS (
        FILTER (
            'Table1',
            [Time] >= windowStart
                && [Time] <= windowEnd
                && [Perimeter] = perim
        ),
        "Measure Value", [Measure]
    )
RETURN
    AVERAGEX ( tempTable, [Measure Value] )
    • StSupQ's avatar
      StSupQ
      Frequent Visitor
      3 years ago

      Hi, 

      Thank you for the proposition.
      However, when I do so, it keeps the filter context of the base table, which filters this calculated table to only the current date. So it will do the average of only one value, which returns the same curve as the underlying measure. 
      What I want to do is to remove the filter context on that calculated table (while keeping it only between the windowStart and windowEnd time variables) and only then calculate the average.

      • johnt75's avatar
        johnt75
        Super User
        3 years ago

        What are you trying to get the average over - over time, over products ?