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,

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.

[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