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.

[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