Dynamic M Query Using Related Field

[cdellapietra]

Hi Folks,

I have the following table in Power BI where 'Codes' is bound to a parameter named 'SelectedCodes':

'SelectedCodes' is used in my DirectQuery to Oracle using string interpolation in there WHERE clause to filter results. This all works fine when I have a slicer on 'Codes', but I'd like to present users with a slicer on 'Category' instead. Switching my slicer from 'Codes' to 'Category' seems to break the Dynamic M query, causing my 'SelectedCodes' parameter to always resort to its default.

Is there a way to achieve a functioning dynamic query when filtering on 'Category'?

[v-kpoloju-msft]

Hi @cdellapietra,

Thanks for reaching out to the Microsoft fabric community forum.

After reviewing the details you provided, here is a few workarounds that might help resolve the issue. Please follow the steps below:

I would also take a moment to personally thank @Akash_Varuna, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

• Please ensure that dynamic M query parameters are enabled in Power BI Desktop. This feature allows report viewers to configure filter or slicer values for an M query parameter.

• To do this, open Power BI Desktop and navigate to Home > Transform data > Transform data to access the Power Query Editor. In the ribbon, select New Parameters under Manage Parameters. Complete the parameter details and reference it in the M query using the Advanced Editor.

• Utilizing dynamic M query parameters can optimize query performance by incorporating filter selections into source queries at the appropriate point.

Please go through the following link for more information about dynamic parameters:
Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

If this post helps, then please give us 'Kudos' and consider Accept it as a solution to help the other members find it more quickly.

Best Regards.

[v-kpoloju-msft]

Hi @cdellapietra,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @cdellapietra,

We have not yet received a response regarding our previous reply and would like to check if your issue has been resolved. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[cdellapietra]

Hi @v-kpoloju-msft 

Apologies, I was away from the office recently. I'm sorry to report neither of the proposed solutions in this thread worked for me. Thank you for your suggestions.

[v-kpoloju-msft]

Hi @cdellapietra, 

Apologize for the delayed response. I reproduced the scenario a, and it worked on my end. I used it as sample data and successfully implemented it.

M Query:

let

    Source = Table.FromRows(

        Json.Document(

            Binary.Decompress(

                Binary.FromText("i45Wck4sSU3PL6pUMFTSUdKIKTUwMDI3NDAwgLB04ALG6AImEJamUqwOkilGKKYYomsygmuKBQA=", BinaryEncoding.Base64),

                Compression.Deflate

            )

        ),

        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Codes = _t]

    ),



    // Step 1: Clean and extract codes from the text string

    #"Removed Parentheses" = Table.TransformColumns(Source, {{"Codes", each Text.Middle(_, 1, Text.Length(_) - 2), type text}}),



    // Step 2: Split comma-separated codes into list

    #"Split Codes" = Table.TransformColumns(#"Removed Parentheses", {{"Codes", each Text.Split(_, "','"), type list}}),



    // Step 3: Expand the lists to new rows

    #"Expanded Codes" = Table.ExpandListColumn(#"Split Codes", "Codes"),



    // Step 4: Clean extra quotes from each code

    #"Removed Quotes" = Table.TransformColumns(#"Expanded Codes", {{"Codes", each Text.Replace(_, "'", ""), type text}}),



    // Step 5: Rename 'Codes' column to 'Code' for clarity

    #"Renamed Columns" = Table.RenameColumns(#"Removed Quotes", {{"Codes", "Code"}})



in

    #"Renamed Columns"

outcome:

I am also including .pbix file for your better understanding, please have a look into it:

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[kdoherty]

@v-kpoloju-msft   Thank you for providing the pbix.  However, this does not address the problem, which is that when you filter by Category (Category 1, Category 2) using a slicer, the resulting values of Code are not passed to the dynamic parameter in Power Query.  It just doesn't work that way. 

[v-kpoloju-msft]

Hi @cdellapietra,

Thank you for providing the update on the issue. If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Best Regards.

[v-kpoloju-msft]

Hi @cdellapietra,

Apologize for the delayed response. I have identified several alternative workarounds that may assist in resolving the thread.

I understand you are using a dynamic M parameter linked to the 'Codes' field. While it functions well with a slicer on 'Codes', switching the slicer to 'Category' causes the query to revert to the default parameter value. This is expected behaviour, as dynamic M query parameters only respond to slicers directly connected to the parameter field (in this case, 'Codes').

Dynamic M Parameter (Directly Bound to 'Codes'): This is your current method, and the only approach supported natively by dynamic M query parameters.
To use it with a different field like 'Category', you would need to redesign the parameter binding to 'Category', but this would mean losing the ability to filter by 'Codes'. For more information, please refer to: Dynamic M Query Parameters – Microsoft Learn

If you want to let users filter by ‘Category’, but still apply the dynamic filter on ‘Codes’ in the M query, here’s an alternative approach:

• Create a disconnected slicer table for 'Category'.
• Use a DAX table or measure to retrieve the related 'Codes' for selected 'Category'.
• Convert that list into a comma-separated string using CONCATENATEX.
• Reference that string inside Power Query (M) and pass it to your native SQL query using string interpolation:

SELECT * FROM your_table

WHERE code IN ('A','B','C')

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @cdellapietra,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-kpoloju-msft]

Hi @cdellapietra,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[kdoherty]

@v-kpoloju-msft   In your proposed solution, I don't understand how you would pass the comma-separated string value computed in DAX using CONCATENATEX to the native M query.  My understanding only slicers bound directly a parameter can pass values back?  

Thank you.

[Akash_Varuna]

Hi @cdellapietra , Could You please try these 

Ensure Relationship: Confirm there's a relationship between "Category" and "Codes" in your data model.
Create a Calculated Table: Use DAX to capture "Codes" filtered by the selected "Category":
     SelectedCodes = DISTINCT(FILTER('YourTable', 'YourTable'[Category] IN VALUES('CategoryTable'[Category])))
Modify Parameter: Update the SelectedCode parameter in Power Query to dynamically use filtered "Codes":
     SelectedCodes = Table.ToList(SelectedCodes, Combiner.CombineTextByDelimiter(","))
Update Query: Pass the dynamic parameter in your SQL WHERE clause:

     SELECT * FROM YourTable
     WHERE Code IN (#"SelectedCodes")
     If this post helped please do give a kudos and accept this as a solution 
     Thanks In Advance