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