Forum Discussion

SHIV_SHANKAR's avatar
SHIV_SHANKAR
New Member
2 months ago

Dynamic M query Date parameter not working for After or before slicer style

Hi,

I am working with an Oracle database using a Dynamic M Query Parameter in Power BI. I have created the parameter and handled it in the M code.

In the Power BI UI, I created a Date table to handle this parameter and bound the date column to the Dynamic M Query Parameter. However, after binding the column to the parameter, the slicer only shows three display styles: Vertical List, Tile, and Dropdown.

I need to use the Before or After date slicer style so users can select a date using the calendar/date picker. If I change the slicer style to Before or After before binding it to the parameter, all slicer options are available. However, once I bind the slicer to the parameter, I receive the following error:

"An incompatible filter is used on a column with parameter mapping."

Is there a way to use a calendar/date picker with a Dynamic M Query Parameter, or is this a limitation of parameter-mapped columns? If there is a workaround, I would appreciate your guidance.

Need Help
Show and Tell
Tips and Tricks

4 Replies

  • Shahid12523's avatar
    Shahid12523
    Community Champion
    2 months ago

    This is a current limitation of dynamic M query parameters—parameter-mapped columns only support equality-based slicers (list, dropdown, and tile), so before, after, between, and calendar date pickers are not supported.

  • v-sathmakuri's avatar
    v-sathmakuri
    Community Support
    2 months ago

    Hi SHIV_SHANKAR ,

    Thank you for reaching out to fabric community.

     

    Thank you Shahid12523  and Kiran-7312 for your prompt response.

    As Shahid12523  said, this is a current limitation of Dynamic M Query Parameters. Once a date column is mapped to an M parameter, Power BI only supports compatible filter operations, so slicer types such as Before, After, Between, and the native calendar/date picker are not supported. This results in the "An incompatible filter is used on a column with parameter mapping" error.

    Currently, Microsoft does not document a supported workaround for using a calendar/date picker with a parameter-mapped column. The supported option is to use the available slicer styles (such as Dropdown, Vertical List, or Tile) for parameter binding.

    If you wanted this feature to be implemented in power bi , I’d also recommend submitting your detailed feedback and suggestions through Microsoft’s official channels, such as the Microsoft Fabric Ideas.  to help bring attention to this limitation.

     

    Thanks!!

  • Kiran-7312's avatar
    Kiran-7312
    Helper I
    2 months ago

    Hi, Could you please share a screenshot of your parameter configuration and the M query where the date filter is applied? For Dynamic M Query Parameters, the parameter data type should match the column data type exactly. If you're using an **After** or **Before** filter, also verify that both the parameter and the source column are using the same **Date** or **Date/Time** type, as a mismatch can prevent the filter from working as expected. If possible, please also let us know: * Which data source you're using (SQL Server, Fabric Warehouse, etc.). * Whether you're using Import or DirectQuery mode. * The complete M query or a sample of it. These details will help identify the exact cause and suggest the correct solution.

    • SHIV_SHANKAR's avatar
      SHIV_SHANKAR
      New Member
      2 months ago

      Hi,

      Here are the details:

      • Source: Oracle (DirectQuery)
      • Parameter type: Date/Time (matches column type)
      • Slicer: Dropdown/List
      • And this is the M code 

        "let
        AsOf = DateTime.ToText(AsOfDate, "yyyy-MM-dd"),
        SQL =
        "SELECT *
        FROM TABLE(get_payoff_mapping_facility(
        DATE '" & AsOf & "',
        '" & P_DOSNUM_FILTER & "'
        ))",

        Source = Oracle.Database(ServerDetails,[Query=SQL])
        in
        Source"