Snowflake sql - __selectAll__ in dynamic M Parameter

[Anonymous]

Hi, 

I'm trying to pass parameter values from a slicer. It is working fine for single select and multi-select. I'm unable to figure out a way to select all slicer values and pass them to query parameters. Can you please help?

In the below query how do I pass all the values when slicer value == '__selectAll__' (selected_all_c_ids)? 

let

    selected_c_ids = if Type.Is(Value.Type(c_id_parameter), List.Type) then
        Text.Combine({"'", Text.Combine(c_id_parameter, "','"), "'"})
    else
        Text.Combine({"'", c_id_parameter , "'"}),

    selected_all_c_ids = if Type.Is(Value.Type(c_id_parameter), List.Type) then
        Text.Combine(c_id_parameter, "__selectAll__")
    else
        false,
    //parameters = Text.Combine({"include_all_c_ids: bool = ", Logical.ToText(selected_all_c_ids) ,",",
                //"c_ids: dynamic = dynamic([", selected_c_ids, "]));"}),

    Source = Value.NativeQuery(
        Snowflake.Databases("X.us-east.snowflakecomputing.com","X_WAREHOUSE",[Role="X_ROLE"])
        {[Name="X_DB"]}[Data], 
        "select distinct v_id from X_DB.X_SCHEMA.X_TABLE
        where c_id IN ("& selected_c_ids &")", null, [EnableFolding=true])
in
    Source

 I looked into the below articles but couldn't find a solution to the above problem. 

https://blog.crossjoin.co.uk/2020/11/08/handling-multi-select-in-power-bi-dynamic-m-parameters/

https://blog.crossjoin.co.uk/2021/10/17/handling-select-all-for-slicers-bound-to-dynamic-m-parameter...

https://blog.crossjoin.co.uk/2021/07/04/dynamic-m-parameters-snowflake-native-sql-and-paginated-repo...

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

https://www.excelguru.ca/blog/2016/04/28/pass-parameters-to-sql-queries/

https://www.sumproduct.com/blog/article/power-bi-tips/power-bi-blog-select-all-operation-now-support...

[Gabriel_Walkman]

Wow is that even possible? Anyways, can you tell us a bit about your situation, why you are doing this?

[Anonymous]

@Gabriel_Walkman If your question is "is it possible to send all the slicer values to the query?" then the answer is yes. It's a new feature released this year. https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

What am I trying to do?

I have a dataset that has millions of rows. I tried to import just one month of data, it took 500MB. So obvious solution is to work on the Direct Query. The problem with Direct Query is you can't do complex DAX which involves more than 1M rows (error: The resultset of a query to an external data source has exceeded the maximum allowed size of ‘1000000’).

My solution is to move the DAX operation to the SQL query section which allows users to select the parameters from the slicer. I'm able to get the result I want except for the select-all feature. 

Why can't optimize the dataset?- I need per user per day granularity. Can't optimize much. 

Why can't process it in the backend using scripts, store the result, and use power BI for visualization and light calculations? - This solution will force me to hard code the lookback period for the analysis. Ex: Show whether a user is New or a Return Visitor within a given time frame? I don't want to hard code the time period and calculate the results in the backend. I want to see new results based on a selected date range. This is just an example not the actual problem.