Forum Discussion
Dynamic M Query doesn't take value from slicer as parameter
Ok, finally I found out the problem.
The is that you cannot have a table for the filter with several columns and showing not only the bindedParamter column in the filter but another field.
So what works:
Simple Slicer where only the field where the binded Parameter is attached is in the filter field (in my case the "egmId" field)
What no works:
Simple slicer where only another field (in my case the "identifier" field) of the filter Table is attached in the filter Fields. So it seems there is no way to show another value in the slicer than using for binded parameters filter...
Hi lenfloge,
The parameter binding mechanism works at the data model level, not the visualization level So when you select a value in the slicer Power BI passes the actual selected value to the parameter (not a mapped/lookup value)
So here is 3 or 4 options or lets called it workarounds you can try it:-
First Option:
- Modify your filter table to show both values:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcuxEcAgCEDRXajDHUREmCVngaL7jxDT5he/e88D00bjtEBRqWfE6FscLVNtNaXhDS5gasqE9MXQrwNz6txJaHddKHsFju0Fw1dIqSVG9R+8ofcX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [egmId = _t, Identifier = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"egmId", type text}, {"Identifier", type text}}), #"Combined Column" = Table.AddColumn(#"Changed Type", "Display", each [Identifier] & " (" & [egmId] & ")", type text) in #"Combined Column"- Then bind the parameter to egmId but use the Display column in the slicer
Second Option:
- Instead of a standard slicer use a Table visual as your selection mechanism:
- Create a table visual with your Identifier column
- Users can click on rows to filter
- The parameter binding will still work with the underlying egmId
Third Option :
- Also consider using customvisuals from AppSource that might handle this mapping better
Final Option :
- If this limitation is a deal breaker you might need to:
Use Import mode instead of DirectQuery
Implement the filtering logic differently
Use a different tool that supports this use case natively
if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
Hi lenfloge,
I hope you are doing well today 🙂
The problem is likely in how you're handling the parameter binding and the M query syntax
So you are issuing 3 Problem as i can see...
First Problem :
- Your current query is vulnerable to SQL injection and has string formatting issues:
"select id, identifier from energiegemeinschaft.energiegemeinschaft where id = '" & parameterEgmId & "'"- You can Fix it by this:
let
Source = Value.NativeQuery(
PostgreSQL.Database("XXXXXXXX", "backend"),
"select id, identifier from energiegemeinschaft.energiegemeinschaft where id = @id",
[id = parameterEgmId],
[EnableFolding=true]
)
in
Source
Second Problem :
- Make sure your parameter parameterEgmId is set to Text type
- The Suggested Values should be Any value (not "List of values")
Your filter table should output simple text values, not complex records:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcuxEcAgCEDRXajDHUREmCVngaL7jxDT5he/e88D00bjtEBRqWfE6FscLVNtNaXhDS5gasqE9MXQrwNz6txJaHddKHsFju0Fw1dIqSVG9R+8ofcX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [egmId = _t, Identifier = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"egmId", type text}, {"Identifier", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type", {"egmId"})
in
#"Removed Other Columns"
- Reconfigure the Binding:
- Go to your filter table query
- In Bind to parameter, select your parameterEgmId
- Set Multi select to No (since your query expects a single value)
Set Select all to No
Another Approach For this Problem you Can just instead of table you could output a list from your filter query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcuxEcAgCEDRXajDHUREmCVngaL7jxDT5he/e88D00bjtEBRqWfE6FscLVNtNaXhDS5gasqE9MXQrwNz6txJaHddKHsFju0Fw1dIqSVG9R+8ofcX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [egmId = _t, Identifier = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"egmId", type text}, {"Identifier", type text}}),
egmIdList = Table.ToList(Table.SelectColumns(#"Changed Type", {"egmId"}))
in
egmIdList- Then bind this list output to your parameter
Finally make sure your main query depends on the parameter:
The parameter should be referenced directly in your main query
The filter table should be evaluated before the main query
Additionally, you may need to differentiate between a single parameter value and a list of values. Best approach is to assume that your report user always provides a list. Use Value.Is to probe it, encapsulate scalars in { } and then in your SQL query use IN.