Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Swalker
Frequent Visitor

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 2
d_gosbell
Super User
Super User

@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
Continued Contributor
Continued Contributor

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

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Kudoed Authors