Forum Discussion
Facing one issue while binding M Query parameter in Slicer
Dear v-karpudapur
Thank you for the reply. For workaround purpose I created one solution where I made a relationship with my main view with selector table so that when Name is selected from main visual (main view) the child and selector visual is showing the ID which is bound to parameter. Now I made ID field background color and text color as Blue so that ID is not visible and when user click on the record then target visual shows data target visual is based on Power M-Query and where condition is the kept as ID=p_Bind_param. But htis is a work around because the selector table is showing one record from visual but when click that record due to filter context the selector table get populated. All though I made selector table tiny so that always one record is displayed but scroll is coming here. I can say it is just a workaround not a concrete solution as per professional look. However actual problem has been taken care with drill through features also alternate introducing buttons in the page can control the execution of the visuals for optimumperformance. But I was exploring the pushdown technique with M-Parameter which is very strong but have a hard limitation. If Microsoft fill this gap that the parameter then most of the users have a huge benefit in direct query performance perspective.
I mean to explain inlittle detail that ID is bind with parameter , ID and Name are kept in selector table so if any facility will be provided that field associated with bind parameter can use the associate name column in slicer whe slicer is selected internally it captures the ID and passed it to M-Parameter to do dynamic push down more efficiently.
Again Thank you very much for your support .
Regards
Jishnu Bhattacharya
Dear krishnakanth240
Thanks for the explanation and I have a same line of understanding with you. If you see my actual problem statement at the begining I have already mentioned that I had done two solutions already . 1st is all visuals in same page working fine but as it is in direct query so some times in page loading the visual get some time to load. Also concurrent execution with 6 users prove that page loading some times respond slowly. Then apply drill through feature where visual UX get modified but performance is optimized and improved a lot. So I was exploring this M query feature if it can be fruitful or not. This is the intention.
Regards
Jishnu Bhattacharya
Hi jishnubhattacha
Thank you for sharing your detailed testing results and findings.
Your understanding is aligned with the current behavior of Dynamic M Query Parameters. Since the parameter must be bound to the underlying column value, using Name in the slicer while keeping the corresponding ID completely hidden is a limitation in this scenario, especially when the ID cannot be exposed due to security requirements.
Based on the performance results you observed, it appears that the drillthrough approach currently provides the most consistent improvement for your DirectQuery workload. While Dynamic M Query Parameters can reduce unnecessary query execution and improve initial page load times, they may not fully address the requirement of filtering by Name without exposing the bound key.
For reference, you may find the following documentation helpful:
Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn
DirectQuery model guidance in Power BI Desktop - Power BI | Microsoft Learn
If you have more questions or need further assistance, please let us know. We're here to help.
Best regards,
Community Support Team.
Dear
Dear v-karpurapudThank you for sharing the materials. It seems that inspite of very good conceptual workaround of Query pushdown technique by M-Query has a lots of limitations.So from your reply the approach which I am trying to explore could not possible.If I need to narrow down data through name/text fields then parameter must be bound with that field. AgreeBut as you had already checked my test scenario after binding with parameter if I am using slicer then I have to keep the ID field not name field. But now I did on trick I made a table visual put the name field from myselector table and ID is in the slicer. When I am clicking on Table visual i.e. on the Name field my slicer is filtering ID. If I minimize the slicer looks almost hidden in the page .Now I want to click on Table visual which can filter slicer and if I click on slicer pushdown takes place and visual is filtered according to ID. But is there any way to avoid the slicer click just click on name slicer gets filtered but query pushdown will take place without clicking on slicer ?Hello jishnubhattacha
Thank you for the additional details.
The scenario you described is a bit different from the original parameter-binding limitation. Here, it's important to check if selecting a Name in the table visual creates a valid single-value filter context on the parameter-bound ID column.
It would be recommended to verify whether the table visual interaction is propagating the required filter context to the bound ID field. If the ID column receives a valid filter context through the visual interaction, Dynamic M Query Parameters should be able to react to that change and trigger query pushdown automatically.
As this behavior depends on how the filter context is applied to the parameter-bound column, further testing is needed to see if the visual interaction provides the filter context required for parameter evaluation and query pushdown.
If you have any further questions, please let us know. we can assist you further.
Regards,
Microsoft Fabric Community Support Team.Hi jishnubhattacha
We wanted to follow up to see if you’ve reviewed the information shared. Has your issue been resolved? If not, could you provide more details so we can help further.Regards,
Microsoft Fabric Community Support Team.