topic Re: The expression specified in the query is not a valid table expression in DAX Commands and Tips

The expression specified in the query is not a valid table expression

Anonymous — Sat, 04 Jan 2020 08:30:26 GMT

Hi, please I am using table variable and when i call the table name "evaluate tbl "with evaluate it works but when I use it in the context of countrows it returns the error above

DEFINE
VAR fsumX =
SUMMARIZE(CHA,[Employee],[YearNo],[Organisation],[MonthNamed],
"CallTimeDuration",SUM(CHA[CallTimeDuration])
)
VAR tbl =
FILTER(
fsumX,
[AvgDBanding] = "Less Than 60 Min(s)" && CHA,[Organisation] = "b" && CHA,[MonthNamed] ="November"
)

EVALUATE

COUNTROWS(tbl)
//tbl

I saw a similar solution that works with countrows (https://social.msdn.microsoft.com/Forums/sqlserver/en-US/42325576-064d-4fbf-bc1c-12fbd6c89728/dax-columns-in-table-variables-cannot-be-referenced-via-tablenamecolumnname-syntax?forum=sqlanalysisservices) but i could not get the quey for mine.

I will appreciate a guide on this

Thanks

Re: The expression specified in the query is not a valid table expression

Anonymous — Sat, 04 Jan 2020 10:41:37 GMT

Hi,

When I try to run the code I get an error on [AvgDBanding] does not exist.

When you add [AvgDBanding] to the FsumX / Summarize function then it will probably work.

It does work for me either as a calculated table or as a measure (with countrows, of course), but I tested it with a very simple test set.

Regards,

Re: The expression specified in the query is not a valid table expression

Anonymous — Sat, 04 Jan 2020 12:27:11 GMT

Thanks for the response, the return works well for me as well, but the "countrows" function on the tbl does not work, I was using the code in dax studio and it was throwing the error, but when I executed the code (countrows)in on power bi it worked, so I am guessing am missing something

Re: The expression specified in the query is not a valid table expression

Anonymous — Sun, 05 Jan 2020 08:51:35 GMT

Sorry, I thought your problem was in PBI.

In Dax Studio you need to use the following to check the result of a measure:

return
ROW("Result:", COUNTROWS(tbl))

Re: The expression specified in the query is not a valid table expression

Anonymous — Sun, 05 Jan 2020 09:39:53 GMT

Anonymous Thanks for the solution, I had a look at the help of dax studio for "row" context but could not find it , can you send me on a link where i can see more on it.

Re: The expression specified in the query is not a valid table expression

Anonymous — Sun, 05 Jan 2020 10:05:04 GMT

Anonymous

There is not too much explanation in the documentation, but it is a starting point: https://docs.microsoft.com/en-us/dax/row-function-dax

Hope this helps.

Re: The expression specified in the query is not a valid table expression

Anonymous — Sun, 05 Jan 2020 10:27:17 GMT

Anonymous Thanks

Re: The expression specified in the query is not a valid table expression

mbbozzuto — Thu, 03 Feb 2022 02:34:59 GMT

Thanks - this solution definitely worked!