Power BI M-Query with TVF execution issue

Hi jishnubhattacha​ ,

Thank you for reaching out to Microsoft Fabric Community Forum, Below are the few points which can resolve your issue. Let us know if you need any further assistance.

The behavior described appears to be related to a limitation of Dynamic M Query Parameters with Drill-through, rather than an issue with the TVF or the parameter expression.

Your requirement of passing a single value such as 1001 or multiple values such as 1001,1002 to the TVF is supported by Dynamic M Query Parameters when the parameter is directly driven by a slicer/filter with multi-select enabled.

However, Drill-through/Cross-drill filters are not supported as inputs to Dynamic M Query Parameters. Therefore, although A2 is correctly shown in the Drill-through filter context on the second page, that value is not being passed to P_param. As a result, your M expression evaluates to ALL, producing:

SELECT * FROM MY_TVF('ALL')

This explains why the Drill-through page displays the correct A2 value while the TVF still receives ALL. As a validation, please test the same P_param using a slicer/filter directly bound to A2:

• Single selection → MY_TVF('1001')
• Multiple selection → MY_TVF('1001,1002')

If this works, it confirms that the TVF and Dynamic M parameter are functioning correctly and that the issue is specifically the Drill-through → Dynamic M parameter handoff.

For the required behavior of selecting A1/A2 in Page 1 and automatically passing the selected A2 value(s) to the TVF after Drill-through, Dynamic M Query Parameters cannot currently provide that integration. The practical options are to use a parameter-bound slicer/filter on the destination page or redesign the query/filtering approach so the parameter value is available before the source query is executed.

One additional point: the current step : Source = #table({"Generated_SQL"}, {{SQL_Query}})

only creates a table containing the SQL text. It does not actually execute the TVF or confirm query pushdown. The actual SQL connector/source step needs to invoke the TVF to validate database-side execution and pushdown.

Microsoft documents Drill-down and Cross-drill filters as unsupported scenarios for Dynamic M Query Parameters:
Microsoft Learn – Dynamic M Query Parameters

Thanks,
Chaithanya.

Hi jishnubhattacha​ ,

Thank you for providing the detailed requirement. Based on the described scenario, the two visuals can remain on the same page; however, there is a limitation with using a Matrix row selection directly to populate a Dynamic M Query Parameter.

Dynamic M Query Parameters cannot use a Matrix click/cross-highlighting interaction to dynamically pass the selected ProductID values to the M parameter. The supported approach would be to use a Category/ProductID filter or slicer as the parameter-driving field. For example:

• When ProductID 1 is selected, the M parameter receives 1 and the TVF executes for ProductID 1.
• When Category A1 is selected, the corresponding ProductIDs {1,2,3,4,5} can be passed through a multi-select M parameter to the TVF.
• When Category A2 is selected, {10,11} can be passed to the TVF.

The TVF result can then be displayed in the second visual on the same page, so a drill-through page is not required. However, if the requirement is specifically to click the Category/Matrix row and automatically pass the corresponding ProductID list to the M parameter without using a slicer or filter, this interaction is not supported with Dynamic M Query Parameters.

Therefore, for the current requirement, we recommend using a Category/ProductID filter or slicer to drive the multi-select M parameter, which will then pass the selected ProductID values to the TVF and return the required data to the visual on the same page.

Thanks,
Chaithanya.

Hi jishnubhattacha​ ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

Regards,
Chaithanya

Dear Chaithanya,

Thank you for the explanation. So you have mentioned that passing M-query parameter to drill through page is currently not supported in Power BI.

OK no issue. I will put the  visual where I applied pushdown technique in same page. say page 1.

Now I am giving you a diagrammatic approach of my objective

I am trying to pass product id=1 when I click on 1st record where A1/1 so 1 is passed in bind parameter. consider productid is bind with testparam

when click on only category A1 then I want to pass product id as 1,2,3,4,5 and TVF will accept 

this and execute and furnish the result.  Now I want to keep both visual in same page no drillthrough. Please guide me how can I accomplish the query pushdown with M-Query by parameter bind using TVF. 

Dear Chaitanya,

Thank you for the update. I had divided the visuals into multiple pages like drill through option and introduce a slicer in top of the second page. so the matrix selection automatically filtered out the designated and relevant values into slicer and click on slicer I have executed dynamic M-query with TVF first but TVF has one concern while passing multiple string values as a parameter. So I have created one standalone view and then use the M-query parameter in where condition with Text.Combine approach and get the result with optimum performance rather I can say controlled execution. 

But just confirm this approach  is solely working in direct query mode only. Not in Composite mode.

Because I have a date range slicer which  takes value from a static table which is in import mode and rest of the queries are in direct query mode. main visual get filtered based on date range so dynamic M-Query is not working there but the report which is completely designed in direct query  is working properly with  M-Parameter.

But Microsoft documentation is not written that M-Query does not work in composite model cases. so if I want to use it in composite model then that visual is not dependent on any static import tables.

Please confirm my understanding is proper or not according to my observation and my experience of facing issues.

Regards

Jishnu Bhattacharya

Hi jishnubhattacha​ ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

Regards,
Chaithanya