Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Dynamic M Query Using Related Field

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

 

Need Help

12 Replies

Replies have been turned off for this discussion
  • Akash_Varuna's avatar
    Akash_Varuna
    Super User
    1 year ago

    Hi Anonymous , 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

     

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Community Support
    1 year ago

    Hi Anonymous,

    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's avatar
      v-kpoloju-msft
      Community Support
      1 year ago

      Hi Anonymous,

       

      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's avatar
        v-kpoloju-msft
        Community Support
        1 year ago

        Hi Anonymous,

         

        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.