Forum Discussion
Dynamic 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.
Hi rmcconnell340
could you please try this m-query
M-Query:
create a table with all unique values of product keys as Table
Finalstring query:
let
Source = Table,
Custom1 = Table.Column(Source,"Product Keys"),
Custom2 = List.Transform(Custom1, each "'" & _ & "'"),
Custom3 = "[" & Text.Combine(Custom2,",") & "]"
in
Custom3
Modification:
// Convert the list of keys into a BigQuery-compatible array format
finalKeysText = If isSelectAll then
// finalstring(from pquery)
finalstring
I have attached sample pbix file with sample data for reference.
If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
Anonymous I am not seeing the Power BI file...could you re-share it?
Also, will this work dynamically? So that if the table of product keys is filtered to a smaller list in the report (say I select all Organic items) and a new list of items is generated in the slicer where I am selecting "Select All" will it just run the list of organic items or all product keys that exist in that table?
Thank you!
Hi rmcconnell340
As you mentioned, the single select and multi select options are already working. In case you need to select all, you can use the above query. I have attached a sample Pbix file.
Thank you.Anonymous I tried you solution in the following code and received an error. I also noticed you are concatenating all the values in DAX formula. Is there a way to load the values from the DAX measure into a Google BigQuery Table Function? Otherwise I don't have a way to take the list of "all selected filtered values" and plugging them back into the TF?
Query:let // Determine if vProductKey is a list or a single value isList = Type.Is(Value.Type(vProductKey), List.Type), // Check if "SelectAll" is selected isSelectAll = if isList then List.Contains(vProductKey, "__SelectAll__") else (vProductKey = "__SelectAll__"), // If "SelectAll" is chosen, use all values from distinctUPC finalKeys = if isSelectAll then Fullstring else if isList then vProductKey else { vProductKey }, // Convert the list into a properly formatted BigQuery array finalKeysText = Text.Combine(List.Transform(finalKeys, each """" & Text.From(_) & """"), ", "), // Construct SQL query with UNNEST function vQuery = "SELECT DISTINCT * FROM `mytable`([" & finalKeysText & "])", // Execute the query using Value.NativeQuery against BigQuery Source = Value.NativeQuery( GoogleBigQuery.Database([BillingProject = "spins-retail-solutions"]) {[Name = "spins-retail-solutions"]}[Data], vQuery, null, [EnableFolding = true] ) in Source