Forum Discussion
Helper IDynamic M Parameters with SelectAll List of Values
Hi rmcconnell340
If the issue is still unresolved, we recommend raising a support ticket.To create a support ticket for Fabric and Power BI, please refer to the steps outlined in the following guide:How to create a Fabric and Power BI Support Ticket - Power BI | Microsoft Learn
If this helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
Thank you.
Helper IAnonymous I am a little confused with this solution. Are Steps 2 & 3 a different Query or is Step 3 supposed to be the result if SelectAll is selected? If Steps 2 & 3 are a different query do I have to write something to let Power BI know to use the first query when SelectAll is not enabled and Steps 2 & 3 when Select All is enabled?
If I change [EnableFolding = true]) to [EnableFolding = false]) then it will error out on me and tell me that this is not supported by DirectQuery.
I am also getting an error based on the size of the list of values this generates, so the solution may not work either unfortunately.
Hi rmcconnell340 ,
Create two text variables one for when its select all and other is for when there is a single or multi selection.
below string for when condition selectall is met, use the table with distinct product key column for all, you can modify this code based on your select all, select single or selectmultiple using if then to return the productkeys
selectall:
let
Source = RenamedTable,
Custom1= Table.Column(Source, "ProductKeys"),
Custom2= List.Transform(Custom1, each """" & _ & """"),
FinalKeysText = Text.Combine(Custom2, ", "),
QueryText = [" & FinalKeysText & "]
in
QueryText
Keep this as a seperate query for now and pass it to the productkey parameter in below code.
Use the following for your connector code
let
source = GoogleBigQuery.Database([BillingProject = "spins-retail-solutions"]) ,
db=Source{[Name="spins-retail-solutions"]}[Data],
NativeQuery= Value.NativeQuery(db, "SELECT DISTINCT *
FROM mytable
WHERE ProductKey IN ( SELECT ProductKey from UNNEST(JSON_QUERY_ARRAY(@productkey)))",
[productkey = QueryText],
[EnableFolding = True])
in
NativeQuery
I hope this helps š