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
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