M Query filter

[richard_wylde]

Hello, I am using the following  document to create an M Query Parameter but can't get it to work. 

In Query Editor, the source query (direct query, not import) runs fine with the custom sql using the parameter :-

 WHERE [STATUS] =  ' "&statusx&" '  "

and this returns data fine from a visualisation.  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'  so that the column is binded to the parameter.

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 following error  , any idea wht I'm doing wrong ?   - 

(   Here is the pbix  )

Error Message:
Query (2, 14) The syntax for 'status' is incorrect. (DEFINE
  MPARAMETER status =
    "C"

  VAR __DS0FilterTable =
    TREATAS({"C"}, 'filter table'[filter_column])

  VAR __DS0Core =
    CALCULATETABLE(
      SUMMARIZE(
        'CHESHDATASYS\DAVIDD T Quotes (3)',
        'CHESHDATASYS\DAVIDD T Quotes (3)'[Co],
        'CHESHDATASYS\DAVIDD T Quotes (3)'[STATUS]
      ),
      KEEPFILTERS(__DS0FilterTable)
    )

  VAR __DS0PrimaryWindowed =
    TOPN(
      501,
      __DS0Core,
      'CHESHDATASYS\DAVIDD T Quotes (3)'[Co],
      1,
      'CHESHDATASYS\DAVIDD T Quotes (3)'[STATUS],
      1
    )

EVALUATE
  __DS0PrimaryWindowed

ORDER BY
  'CHESHDATASYS\DAVIDD T Quotes (3)'[Co],
  'CHESHDATASYS\DAVIDD T Quotes (3)'[STATUS]).

[richard_wylde]

Thanks @v-janeyg-msft , the value exists in the parameter and the database, so unsure why it's not matching

[v-janeyg-msft]

Hi, @richard_wylde 

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.

Best Regards

Janey Guo

[richard_wylde]

Hi @v-janeyg-msft 

Perhaps the data's not available because it is Direct Query - I've re-attcahed  the PBIX here and here is a screenshot , the lower slicer is not working

thank you  

[v-janeyg-msft]

Hi, @richard_wylde 

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:

where status =Text.Insert( """&&""",2,status)

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[richard_wylde]

Thanks @v-janeyg-msft  - no luck unfortunately, I changed it to :-

where b.status =Text.Insert( """&P_STATUS&""",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]

Hi, @richard_wylde 

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.