topic Re: AverageX over Table Variable in DAX Commands and Tips

AverageX over Table Variable

Anonymous — Fri, 10 Feb 2023 06:20:42 GMT

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

Re: AverageX over Table Variable

wdx223_Daniel — Fri, 10 Feb 2023 06:38:19 GMT

RETURN

AVERAGEX(tableone,[Mo])

Re: AverageX over Table Variable

Anonymous — Fri, 10 Feb 2023 14:23:34 GMT

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

Re: AverageX over Table Variable

tamerj1 — Fri, 10 Feb 2023 23:15:30 GMT

Anonymous

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

Re: AverageX over Table Variable

Anonymous — Sat, 11 Feb 2023 00:00:54 GMT

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

Re: AverageX over Table Variable

Anonymous — Sat, 11 Feb 2023 00:02:56 GMT

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.