The expression specified in the EVALUATE statement is not a valid table expression

Question

I'm working on a Tabular cube in Visual Studio. I have a DAX formula in the cube that works fine:

SUMX(FILTER(factFHA, factFHA[EventCd]="D"), [LoanCount])

When I run it in SSMS as:

evaluate(
    SUMX(FILTER(factFHA, factFHA[EventCd]="D"), [LoanCount])
)

it fails with following error:

Query (1, 1) The expression specified in the EVALUATE statement is not a valid table expression.

I have 2 other formulas that both work fine:

evaluate(factFHA)

evaluate(filter('factFHA', [EventCd] = "D"))

I can't figure out what is wrong with the SUMX with FILTER

Please advise. Thank you.

Answer 1

EVALUATE function only works if you pass a table or an expression that returns a table, you are passing a SUMX function which return a scalar value (Decimal).

The syntax to write queries using DAX, is as follows:

[DEFINE {  MEASURE <tableName>[<name>] = <expression> } -> Define a session (optional) measure
EVALUATE <table>  --> Generate a table using your measures or creating calculated columns
[ORDER BY {<expression> [{ASC | DESC}]}[, …] --> Order the returned table by a passed column or expression 
[START AT {<value>|<parameter>} [, …]]] --> This is an ORDER BY Sub-clause to define from which the query results will start.

Define your measure then use then use it inside the EVALUATE clause using a expression that evaluates to a table.

DEFINE
MEASURE factFHA[MyMeasure] =
    SUMX ( FILTER ( factFHA, factFHA[EventCd] = "D" ), [LoanCount] )
EVALUATE
( SUMMARIZE ( FILTER ( factFHA, factFHA[EventCd] = "D" ), factFHA[AnyColumnToGroup]
              , "Sum of MyMeasure", SUM ( factFHA[MyMeasure] ) ) )

Let me know if this helps.