WHERE Syntax Error - Query preparation failed. (Power BI Report Builder)

[Swalker]

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

[d_gosbell]

@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 Learn

EVALUATE SUMMARIZECOLUMNS('ReferenceList'[Index],'ReferenceList'[Year], 'ReferenceList'[ClientType], 'ReferenceList'[Location], 'ReferenceList'[Project], 'ReferenceList'[Sector], 'ReferenceList'[Location] = "Europe") 

[R1k91]

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.

ORDERBY - DAX Guide