DirectQuery · Dynamic M Parameters · Power BI · SQL Server · Synapse

TSQL Table-Valued Functions And Dynamic M Parameters In Power BI DirectQuery

My favourite – and it seems many other people’s favourite – new feature in the February 2022 Power BI Desktop release is support for more datasources (including SQL Server, Azure SQL DB and Synapse) with dynamic M parameters. In my opinion dynamic M parameters are extremely important for anyone planning to use DirectQuery: they give you a lot more control over the SQL that is generated by Power BI and therefore give you a lot more control over query performance.

Teo Lachev has already stolen my thunder and blogged about how the new functionality allows you to use a TSQL stored procedure as the source of a table in DirectQuery mode. In this post I’m going to show you something very similar – but instead of using a stored procedure, I’m going to show a simple example of how to use a TSQL table-valued function, which I think has a slight advantage in terms of ease-of-use.

To start off I created a table-valued function in the Adventure Works 2017 sample database on my local SQL Server which returns some filtered data from the DimDate table:

CREATE FUNCTION [dbo].[udfDates] (
    @day_name varchar(50),
	@month_name varchar(50)
        FullDateAlternateKey, EnglishDayNameOfWeek, EnglishMonthName, CalendarYear

Here’s how it can be called in a SQL SELECT statement:

	udfDates('Thursday', 'February')

Next, I created a Power Query query in my dataset that called this function and returned a table in DirectQuery mode. The great thing about table-valued functions is that they appear in the Navigator pane when you connect to a SQL Server database from the Power Query Editor:

You can only hard-code the values you pass to the function’s parameters at this point but even if you do nothing here you can just return an empty table. After having done this I selected DirectQuery mode:

After this I created two Power Query text parameters, called DayName and MonthName, to hold the month and day names to be passed to the function:

I also created two Import mode queries called DayNames and MonthNames to hold all the valid values for the DayName and MonthName parameters:

The last thing to do in the Power Query Editor was to edit the query calling the function to pass the M parameters to it. Here’s the M code for the query after the modification made to the “Invoked Functiondbo_udfDates1” step:

  Source = Sql.Database(
  dbo_udfDates = Source
        Schema = "dbo", 
        Item   = "udfDates"
  #"Invoked Functiondbo_udfDates1"
    = dbo_udfDates(DayName, MonthName)
  #"Invoked Functiondbo_udfDates1"

At this point I closed the Power Query Editor and loaded the three tables to my dataset:

Next I bound the EnglishDayNameOfWeek column on the DayNames table to the DayName M parameter and the EnglishMonthName column on the MonthNames table to the MonthName M parameter:

Finally, I built a report with two slicers bound to the two dynamic M parameter columns and a table showing the output of the table-valued function:

Here’s an example of the TSQL generated by Power BI to populate the table in this report:

TOP (501) 
select [$Table].[FullDateAlternateKey],
from [dbo].[udfDates]('Thursday', 'February') as [$Table]
 AS [t0]
[t0].[CalendarYear] = 2010

ORDER BY [t0].[FullDateAlternateKey]

I am by no means an expert in writing efficient TSQL so I can’t comment on the pros and cons of table-valued functions, stored procedures or using native SQL queries in Power BI (although the last of these has obvious maintainability issues). Hopefully, though, you can see the possibilities – and if you do get round to using this approach on a project, please let me know how you get on!

25 thoughts on “TSQL Table-Valued Functions And Dynamic M Parameters In Power BI DirectQuery

  1. Thanks Chris. Do you know if PBI uses query folding on custom sql (on all sources) ? If it does and it’s smart i.e. detects a single SELECT statement and adds or expand the WHERE predicate, table-value functions (w or w/o M parameters) has another big advantage over stored procedures as they could be query folded i.e. “CALL MyProc(MyParam) WHERE year=2005” is not possible.
    Not relevant in your example use but consider a table function that returns parametrized) 1M rows…

    1. Answering for Google BigQuery, for which the PBI/PQ connector supports M parameters for some time, … it unfortunately does NOT support query-folding on custom/native SQL. Forcing for it does not help.
      = Value.NativeQuery(GoogleBigQuery.Database([Data], “select * from tmp.table_func(‘foo’, ‘bar’)”, null, [EnableFolding=true])
      Next step, filtering on the year in PQ will still load all and filter locally.

      1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
        Chris Webb says:

        Not everything can be folded in Import mode when you use a native SQL query. However, if you’re talking about DirectQuery (which we are here) on a fairly simple select statement I would expect to see an error message rather than “local filtering” taking place, which I’m pretty sure isn’t even possible in DirectQuery mode for this kind of scenario. How do you know query folding isn’t taking place?

      2. I assumed based on the fact that in PQ, the filtering step has “View Native Query” grayed out, and yes in DQ mode. However looking at the super useful “Diagnose” output, I do see indeed an Execute Command with the folded query ! Conclusion, if not obvious (e.g. response time or ‘view native query’ option enabled), look at the diagnostics or in the logs on the data source side.
        Having query-folding + M-parameters with table functions allows to push even more to the rdbms – for us, that’s using the most out of BigQuery BI Engine (in-mem).

  2. Can this be used to in a native query? Current having an issue making this works. Followed: Dynamic M query parameters in Power BI Desktop to set up parameter (have the preview option selected), parameter set up as Date/Any Value/Required. Created a table w/ the a rolling 30 days in DAX, and bound the column to my parameter. Added the Parameter to my direct TSQL query and it works with the parameter using the ‘current value’ in the parameter

    Added date column that is bound to the parameter to a slicer. When I click on a new date in the slicer and hit refresh the data that is return is the same. It seems like the change in the slicer isn’t being reflected in the parameter. If I manually change the parameter using ‘edit parameter’ the data will update to the new date. Is there a setting that I am missing?

  3. Hi Chris,
    Any knowledge of TSQL Table-Valued Functions And Dynamic M Parameters not being supported in Analyze In Excel for PowerBI datasets?
    I am getting different results in PBI desktop & service compared to Analyze In Excel when connecting to the same dataset.
    Thanks in advance for the help.

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      Dynamic M parameters don’t work with MDX queries, which means they don’t work with Analyze in Excel unfortunately

  4. Hello Chris,

    This article is very useful. We are having Function1 to feed data for Page1 and function2 to feed data for Page2. When we drill through from Page1 to Page2, the transient filters are displaying correct values but the data is not filtered in Page2 based on Page1 filters ( transient filters). Please suggest.


    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      You should have shared dimension tables that are used to populate the slicers/filters and which contain the values that are being passed to the functions. If you have then the selections on these should be passed from page to page.

  5. Hello Chris,

    Parameter0 from table0 (Import) is the input for Page 1 function ( DirectQuery)
    Page1 function column is the input for Page 2 function ( DirectQuery)
    We need to drill through from Page1 to Page2 ( Both pages uses dynamic M query functions)

    1. Keep all Filters ON – We can see the transient filter carrying the correct filter value from Page1 and drill through filter set to ALL which is incorrect . If we manually click on the dropdown , we get the correct value listed.

    2. Keep all Filters OFF – We can see only the drill through filter set to ALL . If we manually click on the dropdown , we get incorrect value from default parameter.

    As suggested by you table0 is shared dimension table to Page1 function and Page 2 function .Please suggest.

  6. Thanks Chris! . Having a shared dimension table helped. Also, since we did not have parameter0 inside the matrix in Page1, the selection was not getting carried to Page2.

  7. Hello Chris
    This is helpful. However, I have a requirement, trying to find different ways to do it.
    User wants to pass 2 parameters; one is ID which is unique and the other is salesID. SalesID is a column in the fact table.
    User wants to filter data base on ID and then pass multiple sales ID’s as a single parameter and he does not want to go for list.
    Is it even possible to meet this requiremet? Thank you

  8. Hello Chris,

    We want to understand how DirectQuery datasource setting needs to be configured, as we see CANT DISPLAY VISUAL error in the application after publishing it.

    We get the below message:

    Data source unreachable
    There is no gateway to access the data source . Verify the gateway connection configuration on the dataset settings page and make sure all required data sources are added to the selected data gateway.

    Kindly help.

  9. Hi Chris , The Dynamic Parameters are not working when we publish report in Power BI services , I had created direct query with SQL server and binded the parameter to one of the column .The report is working fine in Power BI desktop file but when I publish the report in Power BI service the report is not loading the data visual error .I had checked all the gateway connection everything is fine. Can you please let me know if direct query with dynamic parameters work on Power BI service

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      What error are you getting?

  10. HI Chris, Is this possible using Power BI? My business user would like to be able to enter a parameter into a BI report, then refresh a query to return just the data entered for the parameter entered. I’ve been looking for a simple example of this and cannot find it anywhere, so I do not think it’s possible when using power BI. Example: business user experience: enter a customerID into a form, hits a button and the power BI report refreshes and returns data on the customerID entered updating all the eye candy charts visuals with data from the customrerID query. The query is simple “Select * From Customer_table where customerID = “customerID entered”

  11. Hi Chris, thank you for this. I am able to use this example to develop something that works perfectly in Power BI desktop but I cannot get it to work in my workspace in Power BI service ( to share with users who would use it. Not sure what the problem is because the error message is like “Unable to connect to the data source ca53-34953-2234232-” etc etc and that’s it. Our administrator does not know what the problem is and suggested that it might require bug testing and it may be due to the data types. Have you encountered something similar?

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      What data source are you using? Can you publish a DQ dataset that doesn’t use dynamic M parameters that works?

      1. The data source is a local onsite prem SQL server (I think version 16) database table. Just tested again using the same table (from same data source, db table) in another direct query just taking top 100 rows without dynamic M parameters and works fine when I publish to Power BI service. For the table-valued function I have basically:

        CREATE FUNCTION [ANALYSTS].[fnchCategorySwitcher]
        — Status cutoff parameters
        — Change cutoff parameters
        SELECT ID
        , PROPORTION
        ELSE 1
        ELSE 1

        I originally declared the parameters as ‘real’ data types, and changing to decimal like this didn’t change the outcome. Do you think there is something I need to check at the power query end, or in the data model end with Power BI? Or should I try this over with a stored procedure instead?

  12. Also, not sure if relevant but also will mention that an on-premises data gateway is registered and running. But, in the settings for the report in Power BI service I note that under ‘Data Source credentials’ there is the line ‘(admin has granted access, credentials are not required)’. However when I look at the docs for DirectQuery it says ‘you still must provide credentials for the Power BI service to open the published report. Without credentials, an error occurs when you try to open a published report’. So wondering if that is something worth investigating or if my interpretation of the documentation is too literal.

  13. This is brilliant and very simple, but I cannot get it to work with dates.

    I have a function which I loaded in as you did. I created two date parameters, and I linked them to two date tables that I loaded in, and added the parameter names in the advanced editor window. When I look at the function in data view it has all of the data showing.

    I add a table visual in report view and the data is showing fine. I add two slicers one linked to the date table with the start parameter linked and another with the end parameter. I then get an error on the visual that says cannot convert date.

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.