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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
richard_wylde
Helper IV
Helper IV

M Query filter

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]).

 

6 REPLIES 6
richard_wylde
Helper IV
Helper IV

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

v-janeyg-msft
Community Support
Community Support

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

 

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  

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.

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.