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

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

Swalker — Sat, 04 Feb 2023 09:40:46 GMT

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

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

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

R1k91 — Sun, 05 Feb 2023 19:27:25 GMT

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

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

d_gosbell — Mon, 06 Feb 2023 07:00:12 GMT

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