Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

AverageX over Table Variable

Been banging my head against the wall searching Google and the forums for similar concept to this issue with no resolution. I can get the first version to work in DAX Studio but can't seem to get the second version to work in PowerBI because of this error: "Table variable 'tableone' cannot be used in current context because a base table is expected.".
I'm guessing I need to know more about Data lineage to really understand how to get this to work properly.

Any advice on areas I should read-up on to continue my learning journey in addition to a solution on this problem would be greatly appreciated. Thanks in advanced 

dax studio version

DEFINE
     TABLE TableVar =

FILTER(
     ADDCOLUMNS(
          VALUES( dDate[FirstofMonth] ),
          "Mo", CALCULATE(DISTINCTCOUNT(fKeywords[SK_index]))
     ),
     [Mo] > 0 && dDate[FirstofMonth] < [var_date_cur_Mo]
)

EVALUATE

{ AVERAGE( TableVar[Mo] ) }

powerbi version

MeasureTest = 

VAR tableone =

FILTER(
     ADDCOLUMNS(
          VALUES( dDate[FirstofMonth] ),
          "Mo", CALCULATE(DISTINCTCOUNT(fKeywords[SK_index]))
          ),
     [Mo] > 0 && dDate[FirstofMonth] < [var_date_cur_Mo]
)
RETURN

     AVERAGE(tableone[Mo])


I should add, I know the correct result I should be getting and I don't really understand why I don't get the correct result with something like this where the measure should be running a DISTINCTCOUNT while iterating over each FirstOfMonth from the date column and then applying the average to that entire column:
     AVERAGEX(
          VALUES( dDate[FirstofMonth] ), 

          CALCULATE(
               DISTINCTCOUNT( fKeywords[SK_index] )
          )
     )


5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      Photos from the results of using your suggested solution do not match the results of other measures. I do not understand why though

       

       

       

  • tamerj1's avatar
    tamerj1
    Community Champion

    Anonymous 

    Calculate tables are not affected by the filter context while measures do. What is the dax code of [var_date_cur_Mo]?

    • Anonymous's avatar
      Anonymous
      Not applicable

      var_date_cur_Mo = EOMONTH(TODAY(),-1)+1

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thinking b/c DAX studio is still executing the EVALUATE and I'm getting the correct result, I just need to figure out what the issue is with the syntax in PowerBI measure so it will provide correct result of 30,109.71.