Forum Discussion

tmendoza's avatar
tmendoza
Resolver I
9 months ago
Solved

Dynamic M Query Parameters not working when I make slicer selection

You folks have always been a great help to me.  What am I doing wrong with my Dynamic M Query Parameter set up? I'm trying to speed up my Direct Query by utilizing Dynamic M Query Parameters. I fo...
Need Help
  • parry2k's avatar
    parry2k
    8 months ago

    tmendoza For everyone, it is fixed, and the solution was to remove "&" from the table name "P&L_GLDQ" 🤷

v-prasare's avatar
v-prasare
Community Support
9 months ago

Hi tmendoza ,

The key detail is that using Sql.Database() with a Query= argument automatically disables query folding. When folding stops, Power BI cannot push the parameter back to SQL, which is why the “View Native Query” option remains disabled even though the script appears to contain only a single step.

To restore folding and enable the dynamic parameter behavior, please replace the current script with the Value.NativeQuery() pattern below:

let
    Source = Sql.Database("MCMC.MadeUpServer.com", "DIY_MadeUpDataBase"),
    DynamicQuery = Value.NativeQuery(
        Source,
        "
            SELECT *
            FROM dbo.vPLGL_ISDashboard
            WHERE Period = @Period
        ",
        [Period = fiscal_period]
    )
in
    DynamicQuery
 

After applying this change, View Native Query should become available, and the slicer-driven parameter will correctly fold back into SQL without triggering the conversion errors.

 

 

 

Thanks,

Prashanth

tmendoza's avatar
tmendoza
Resolver I
9 months ago

Thank you very much for your input.

I think we are very close to having this solved.

I've followed your steps above and it works out in the Power Query editor, (I can see the data load for the correct period).

However, when 'Close & Applying' it to the dashboard report, I run into this:

Once we have the above solved, I think I'll be all set.

  • kushanNa's avatar
    kushanNa
    Super User
    9 months ago

    Nice explanation v-prasare , Enjoyed reading thru your comments 

     

    tmendoza I wonder if this error go back to v-prasare explanation about quotes maybe try removing them as he suggested ? 

     

    try this code ? 

    let
    Source = Sql.Database("MCMC.MadeUpServer.com", "DIY_MadeUpDataBase"),
    DynamicQuery =
        Value.NativeQuery(
            Source,
            "SELECT * FROM dbo.vPLGL_ISDashboard WHERE Period =" & Number.ToText(fiscal_period)
            
        )
in
    DynamicQuery

     

  • tmendoza's avatar
    tmendoza
    Resolver I
    9 months ago

    kushanNa No Dice,

    It eneded up giving me the original 'Conversion' error. (I've pasted it at the bottom)

    I copy and pasted the code. Although it works in the Power Query Editor, the visuals break once I Close & Apply the update to the report. 

    Once again, thanks for all your help so far.

    I really think v-prasare is on to something.

     

     

  • tmendoza's avatar
    tmendoza
    Resolver I
    9 months ago

    Update to the above, I updated v-prasare query to show Period instead of @Period and the data loaded in the Power query editor ok. It also was closed and applied to the report but resulted in the same 'Conversion' error.

    let
    Source = Sql.Database("MCMC.Server.com", "Database"),
    DynamicQuery = Value.NativeQuery(
        Source,
        "
        
            SELECT *
            FROM dbo.vPLGL_ISDashboard
            WHERE Period = Period
        ",
        [Period = fiscal_period]
    )
in
    DynamicQuery