Forum Discussion
SUMX Not Working
I have created a sample dashboard based on the AdventureWorks sample database and I cannot figure out why I cannot get this to work. I can evaluate every VAR throughout except _Result which never works. I have attached a photo of _FilteredTable, which you can see is a very simple table with a total.
(the code snippet is showing up incorrectly, so I've also included a screenshot of the fairly simple code)
DEFINE VAR _TableWithBadNames = SUMMARIZECOLUMNS ( 'Production ProductCategory'[Name], 'Sales SalesOrderHeader'[OrderDate].[Year], "@Total", SUM('Sales SalesOrderDetail'[LineTotal]) ) VAR _Table = SELECTCOLUMNS( _TableWithBadNames, "@Category", [Name], "@Year", [Year], [@Total] ) VAR _FilteredTable = FILTER(_Table, [@Category]="Accessories" && [@Year]=2011) VAR _Result = SUMX(_FilteredTable, [@Total]) EVALUATE _Result
Your query is correct up until EVALUATE. The issue is that EVALUATE expects a table expression, and _Result is a scalar, so the engine cannot return it. Wrap it in either curly braces or ROW and it will work.
EVALUATE { _Result }
or
EVALUATE ROW("Result", _Result)
Once you do that you should see the Accessories 2011 total returned as a single row.
If this helped, a thumbs up and accepting the solution would be appreciated.
Thanks,
Shai Karmani
2 Replies
Your query is correct up until EVALUATE. The issue is that EVALUATE expects a table expression, and _Result is a scalar, so the engine cannot return it. Wrap it in either curly braces or ROW and it will work.
EVALUATE { _Result }
or
EVALUATE ROW("Result", _Result)
Once you do that you should see the Accessories 2011 total returned as a single row.
If this helped, a thumbs up and accepting the solution would be appreciated.
Thanks,
Shai KarmaniDarn it. I spent 4 hours troubleshooting that yesterday! I even kept seeing people using {} in EVALUATE and I couldn't figure out why.
Many thanks
