Facing one issue while binding M Query parameter in Slicer

[jishnubhattacha]

Hello Guys.

To explore direct query optimization technique drill through feature gives a significant outcome and we tested it and got satisfactory response during page loading time next interaction between visuals with  filter context. no slowness has been observed also it is working with satisfactory outcome. based on data Page loading time within 12 secs and interactions among visuals in next page almost within 10  to 12 secs  where page 2 contains 8 visuals and page 1 one visuals and 7 slicers.  So to find other alternative because in drill through we have to split the visuals in two pages or three pages. We try to keep all visuals in single page. By query tuning , query splitting we got more or less satisfactory result but sometimes it takes almost 30 to 40 secs in page loading because all queries get executed and after that filter context are applied.

So now I am exploring dynamic M query handling.

Now I did step by step following.

1. first I  add one view (DIM_DATA_VW) in my model. now instead of adding other views and making relationship now I go to transform option.

2. Then I create one M parameter : P_Param and set initial value as -1

3. Now I create a dynamic M query below

let
Source = Sql.Database("server","db"),
TableData = Source{[Schema="dbo", Item="fact_port_vw"]}[Data],

Filtered =
Table.SelectRows(
TableData,
each Number.From([ID]) = Number.From(P_Param)
)

in
Filtered

4. DIM_DATA_VW  contains lots of dimension fields where ID is the primary key and Name is dimension. there are other columns. same ID exist in FACT_PORT_VW  where ID is a candidate key.

5. Now I created one selector table from my DIM_DATA_VW contains ID and Name.

SampleSelector =

SELECTCOLUMNS(

    'DIM_DATA_VW',

    "ID",  'DIM_DATA_VW'[ID],

    "NAME",    'DIM_DATA_VW'[NAME]

)

6. Next I bind SampleSelector[ID]->P_Param in model view.

7. Then I create a table visual contains the columns of my dynamic M query  i.e. columns of FACT_PORT_VW.

8. After that I created one slicer put SampleSelector[ID] and the slicer only accept single value (single select is On by default due to parameter binding).

Now initial value of parameter is -1 so my table visual is hsowing nothing . accepted behaviour . no time in page loading. in relationship case due to existance of gate filter on visuals visual got hidden but query is executed and it takes some time depending on data volume. but in this case page loading time improves a lot.

but the concern is if I replace ID from slicer and add SampleSelector[Name]  column then select Name cannot filter the visual because it is not supply the ID value into P_Param (bind parameter).

Name is needed as per business point of view because ID is just a numeric field and user cannot understands the ID. So how can I use M querybind parameter logic with selecting Name from slicer and it internally set ID into P_Param?

Moreover for a workaround I created another table visual and keep Name field on it. When I click on Name the slicer filters the ID and when I select on slicer then filtering is taking place and visual shows data . could youplease guide me I jsut have one missing gap if it  is done then I have another optimum solution for direct query.  I think it is a limitation of dynamic M query but need your help.

Regards

Jishnu Bhattacharya

[pcoley]

@jishnubhattacha Your current workaround works partially: Clicking on the Name visual filters the ID slicer because of the relationship/filter propagation in the model. But it's indirect and not as seamless. The Display Name column removes that friction.

There is a gap with Dynamic M Query Parameters: The parameter binding only passes the value of the specific column you bind to (in your case, SampleSelector[ID]). It does not automatically do a lookup from Name → ID.

Please try using a measure, for example:

Display Name = 
VAR _Name = 'SampleSelector'[NAME]
VAR _ID = 'SampleSelector'[ID]
RETURN
    IF(
        ISBLANK(_Name),
        FORMAT(_ID, "0"),
        _Name & " (" & _ID & ")"
    )

Put the new Display Name column in your slicer (instead of raw Name or ID).

Keep the parameter binding on SampleSelector[ID] (do not change this).

Set the slicer to Single select.

--> When the user selects a friendly "Name (ID)" value, Power BI still passes the underlying ID value from the bound column to P_Param. 

[krishnakanth240]

Hi @jishnubhattacha 

Can you try making the parameter bound to ID and create a display column as Display = [Name] & " (" & [ID] & ")" and use this column in slicer while keeping ID as bound parameter column

Power BI might not support showing one field Name while passing another field ID to a dynamic M parameter slicer

[jishnubhattacha]

I have replied on basis of your guideline. the combine fields Name + ID already I had checked and after that I initiated this communication. It was not also working.

[jishnubhattacha]

I have checked  but the problem is ID should not be visible in slicer. User do not allowed to see ID's it becomes a security issue. because those ID value are the primary key of the tables.

[krishnakanth240]

Understood, if ID cannot be exposed in the slicer due to security requirements, Dynamic m query parameters may not be suitable for this scenario as bound parameter value need to be available in slicer. You might need to explore an alternative filtering approach or handling ID mapping in source or query layer

[jishnubhattacha]

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

[v-karpurapud]

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.

[jishnubhattacha]

Dear  

Dear v-karpurapud

 Thank 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. Agree

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

[v-karpurapud]

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.

[jishnubhattacha]

Thank you Team,

If I will provide you the situation would you please like to help me. My intention is to get almost alternatives which  can cater the business scenario if any performance are reported. So far I explored this feature has a potential to serve performance related affairs which we find in different queries regarding Direct Query related matters.

Regards

Jishnu Bhattacharya

[v-karpurapud]

Hi @jishnubhattacha 

Absolutely. If you can share the business scenario, data volume, model design, DirectQuery source, number of visuals, filters/slicers, and the specific performance challenges you are observing, it would be easier to understand the requirements and discuss possible approaches.

As you have already demonstrated through your testing, Dynamic M Query Parameters can be a powerful option in certain DirectQuery scenarios. However, depending on the requirements, there may be other approaches worth considering as well, such as drillthrough, composite
models, aggregations, query optimization, visual interaction control, or report design changes.

Feel free to share the details of your scenario, and we can discuss which options might best fit your requirements.

Regards,

Microsoft Fabric Community Support Team.

[v-karpurapud]

Hi @jishnubhattacha 

Just checking in as we haven't received a response to our previous message. If you have any further questions, please let us know.  We can assist you further.

Regards,

Microsoft Fabric Community Support Team.

[v-karpurapud]

Hi @jishnubhattacha 

We were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank You.

[v-karpurapud]

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.

[jishnubhattacha · Accepted solution]

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

[v-karpurapud]

Hi @jishnubhattacha 
Thank you for sharing the details of your workaround and your testing results.

The selector table method does achieve the intended behavior, though, as you noted, it is more of a workaround than a fully production-ready solution because of the extra visual and UX considerations.
From your tests so far, the drillthrough approach and controlled visual execution offer a more practical and consistent optimization path for your DirectQuery scenario.The enhancement you mentioned letting users pick a friendly display value like Name while passing the corresponding ID to the Dynamic M Query Parameter would make this feature more flexible, especially in cases where key values shouldn’t be visible to end users.

If you believe this capability would help your use case, you might consider submitting it to the Power BI Ideas forum, where ideas with strong community support are reviewed by the product team for possible future updates.

You can submit a Idea through the Microsoft Power BI Ideas Forum:
Fabric Ideas - Microsoft Fabric Community

Regards,
Microsoft Fabric Community Support Team.