Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 )
Thanks @v-janeyg-msft , the value exists in the parameter and the database, so unsure why it's not matching
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
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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |