Forum Discussion
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 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.
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
16 Replies
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
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.
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
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 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.
