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
@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")
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.
User | Count |
---|---|
7 | |
3 | |
1 | |
1 | |
1 |