Forum Discussion
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
- wdx223_DanielCommunity Champion
RETURN
AVERAGEX(tableone,[Mo])
- AnonymousNot applicable
Photos from the results of using your suggested solution do not match the results of other measures. I do not understand why though
- tamerj1Community Champion
Anonymous
Calculate tables are not affected by the filter context while measures do. What is the dax code of [var_date_cur_Mo]?
- AnonymousNot applicable
var_date_cur_Mo = EOMONTH(TODAY(),-1)+1
- AnonymousNot 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.