Forum Discussion

jishnubhattacha's avatar
jishnubhattacha
Helper I
2 months ago
Solved

Facing one issue while binding M Query parameter in Slicer

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 interacti...
Interesting Links
Show and Tell
Tips and Tricks
Tutorial Requests
  • jishnubhattacha's avatar
    jishnubhattacha
    2 months ago

    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

pcoley's avatar
pcoley
Super User
2 months ago

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.