M Query filter

Question

Hello, I am using the following &nbsp;document to create an M Query Parameter but can't get it to work.&nbsp;In Query Editor, the source query (direct query, not import) runs fine with the custom sql using the parameter :-&nbsp;&nbsp;WHERE [STATUS] =&nbsp; ' "&amp;statusx&amp;" '&nbsp; " &nbsp;and this returns data fine from a visualisation.&nbsp; I have created another table with a column containing the same values as the parameter, in order to use to filter on, and setting the 'Bind to Parameter'&nbsp; so that the column is binded to the parameter.&nbsp;However when this table is then used as a slicer in order to filter the parameter choice, the visualistion of the source query returns the&nbsp;following error&nbsp; , any idea wht I'm doing wrong ? &nbsp; -&nbsp;&nbsp;( &nbsp; Here is the pbix&nbsp; )&nbsp;Error Message:Query (2, 14) The syntax for 'status' is incorrect. (DEFINE&nbsp; MPARAMETER status =&nbsp;&nbsp;&nbsp; "C"&nbsp; VAR __DS0FilterTable =&nbsp;&nbsp;&nbsp; TREATAS({"C"}, 'filter table'[filter_column])&nbsp; VAR __DS0Core =&nbsp;&nbsp;&nbsp; CALCULATETABLE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SUMMARIZE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'CHESHDATASYS\DAVIDD T Quotes (3)',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'CHESHDATASYS\DAVIDD T Quotes (3)'[Co],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'CHESHDATASYS\DAVIDD T Quotes (3)'[STATUS]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; KEEPFILTERS(__DS0FilterTable)&nbsp;&nbsp;&nbsp; )&nbsp; VAR __DS0PrimaryWindowed =&nbsp;&nbsp;&nbsp; TOPN(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 501,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; __DS0Core,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'CHESHDATASYS\DAVIDD T Quotes (3)'[Co],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'CHESHDATASYS\DAVIDD T Quotes (3)'[STATUS],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp; )EVALUATE&nbsp; __DS0PrimaryWindowedORDER BY&nbsp; 'CHESHDATASYS\DAVIDD T Quotes (3)'[Co],&nbsp; 'CHESHDATASYS\DAVIDD T Quotes (3)'[STATUS]).&nbsp;

v-janeyg-msft · Answer

Hi,&nbsp;richard_wylde&nbsp;&nbsp;The sample data I downloaded can't be opened. Can you share sample data and error images in onedrive for business? So we can help you soon.&nbsp;Best RegardsJaney Guo

richard_wylde · Answer

&nbsp;Hi&nbsp;v-janeyg-msft&nbsp;&nbsp;Perhaps the data's not available because it is Direct Query - I've re-attcahed&nbsp; the PBIX here and here is a screenshot , the lower slicer is not working&nbsp;thank you &nbsp;

v-janeyg-msft · Answer

Hi,&nbsp;richard_wylde&nbsp;&nbsp;I'm sorry that I can't open your file and can't see any data because you use DQ, so I can't visually see the problem.You can try to modify your formula:&nbsp;where status =Text.Insert( """&amp;&amp;""",2,status)&nbsp;Best RegardsJaney Guo&nbsp;If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

richard_wylde · Answer

Thanks v-janeyg-msft&nbsp; - no luck unfortunately, I changed it to :-where b.status =Text.Insert( """&amp;P_STATUS&amp;""",1,b.status)but it says the default value of the parameter does not exist :-DataSource.Error: PostgreSQL: 42703: column "JASON1" does not exist

v-janeyg-msft · Answer

Hi,&nbsp;richard_wylde&nbsp;&nbsp;I’m not sure what the custom sql you want is, but according to the error message, it should be a parameter problem, causing the database to find the corresponding column.

Forum Discussion

M Query filter

6 Replies

Related content

filter by date on Odata query

Filter

merging direct query queries

OData query filter by results from another query

DAX Query help