topic Re: Does ALL function accept table variable? in DAX Commands and Tips

Does ALL function accept table variable?

sandeepsingh — Tue, 21 Jul 2020 17:39:25 GMT

HI,

I tried to create a table in dax using the below code, I am getting a error message : 'The ALL function expects a table reference for argument '1', but a table expression was used.' Am I missing something here.

Table =

Var MyTable = ALL(Sales)

Return

ALL(mytable)

Re: Does ALL function accept table variable?

AntrikshSharma — Tue, 21 Jul 2020 17:51:08 GMT

Nope, you are not allowed to use table expressions. but since you have assigned the table to the variable you don't need to use ALL again.

Table = Var MyTable = ALL(Sales) Return MyTable

Re: Does ALL function accept table variable?

Greg_Deckler — Tue, 21 Jul 2020 17:53:23 GMT

Technically, there is no context that would filter the table so all you need is:

Table = 'Sales'

It's automatically "all" because there is no filter or row context.

Re: Does ALL function accept table variable?

sandeepsingh — Tue, 21 Jul 2020 18:00:10 GMT

Honestly, I am building a complex dax query, wherein I am creating a table using summarize and add columns, I have stored the resultant table in a variable. But when I using the stored table variable again, I m getting the table expression error. ALL does not accept table variable, and what does actually is difference between table expression and a table reference.

Re: Does ALL function accept table variable?

AntrikshSharma — Tue, 21 Jul 2020 18:07:39 GMT

I think that is because variables are static and when you store a value/table in a variable it is stored once it is evaluated and post that you can not change its contents,

Re: Does ALL function accept table variable?

sandeepsingh — Tue, 21 Jul 2020 18:19:32 GMT

I found the solution in the DAX documentation - 'The argument to the ALL function must be either a reference to a base table or a reference to a base column. You cannot use table expressions or column expressions with the ALL function.' So that the column has to be physically present in the model.