Forum Discussion
WHERE Syntax Error - Query preparation failed. (Power BI Report Builder)
Hi,
In Report Builder this statement works fine using ORDER BY :
EVALUATE SUMMARIZECOLUMNS('ReferenceList'[Index],'ReferenceList'[Year], 'ReferenceList'[ClientType], 'ReferenceList'[Location], 'ReferenceList'[Project], 'ReferenceList'[Sector]) ORDER BY 'ReferenceList'[Location] ASC
BUT when i try to filter on Location = 'Europe ' using the WHERE statement it produces an syntax error
EVALUATE SUMMARIZECOLUMNS('ReferenceList'[Index],'ReferenceList'[Year], 'ReferenceList'[ClientType], 'ReferenceList'[Location], 'ReferenceList'[Project], 'ReferenceList'[Sector]) WHERE 'ReferenceList'[Location] = 'Europe'
TITLE: Power BI Report Builder
------------------------------
Query preparation failed.
------------------------------
ADDITIONAL INFORMATION:
Query (1, 180) The syntax for '<ccon>WHERE</ccon>' is incorrect. (<ccon>EVALUATE SUMMARIZECOLUMNS('ReferenceList'[Index],'ReferenceList'[Year], 'ReferenceList'[ClientType], 'ReferenceList'[Location], 'ReferenceList'[Project], 'ReferenceList'[Sector]) WHERE 'ReferenceList'[Location] = 'Europe'</ccon>). (Microsoft Analysis Services)
------------------------------
BUTTONS:
OK
------------------------------
Help is appreciated
Thanks
2 Replies
ORDERBY is a valid DAX query keyword whereas WHERE is not (it's a SQL keyword).
use filter or calculatetable to apply filter according to your needs.
R1k91 is correct, there is no WHERE keyword in DAX
But, you can also include filters in the SUMMARIZECOLUMNS see SUMMARIZECOLUMNS function (DAX) - DAX | Microsoft LearnEVALUATE SUMMARIZECOLUMNS('ReferenceList'[Index],'ReferenceList'[Year], 'ReferenceList'[ClientType], 'ReferenceList'[Location], 'ReferenceList'[Project], 'ReferenceList'[Sector], 'ReferenceList'[Location] = "Europe")
