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)
)
RETURNS TABLE
AS
RETURN
SELECT
FullDateAlternateKey, EnglishDayNameOfWeek, EnglishMonthName, CalendarYear
FROM
DimDate
WHERE
EnglishDayNameOfWeek=@day_name
and
EnglishMonthName=@month_name;
Here’s how it can be called in a SQL SELECT statement:
select
FullDateAlternateKey,
EnglishDayNameOfWeek,
EnglishMonthName,
CalendarYear
from
udfDates('Thursday', 'February')
where
CalendarYear=2005
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:
let
Source = Sql.Database(
"localhost",
"AdventureWorksDW2017"
),
dbo_udfDates = Source
{
[
Schema = "dbo",
Item = "udfDates"
]
}
[Data],
#"Invoked Functiondbo_udfDates1"
= dbo_udfDates(DayName, MonthName)
in
#"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:
SELECT
TOP (501)
[t0].[FullDateAlternateKey],
[t0].[EnglishDayNameOfWeek],
[t0].[EnglishMonthName]
FROM
(
(
select [$Table].[FullDateAlternateKey],
[$Table].[EnglishDayNameOfWeek],
[$Table].[EnglishMonthName],
[$Table].[CalendarYear]
from [dbo].[udfDates]('Thursday', 'February') as [$Table]
)
)
AS [t0]
WHERE
(
[t0].[CalendarYear] = 2010
)
GROUP BY
[t0].[FullDateAlternateKey],[t0].[EnglishDayNameOfWeek],
[t0].[EnglishMonthName],[t0].[CalendarYear]
ORDER BY [t0].[FullDateAlternateKey]
ASC
,[t0].[EnglishMonthName]
ASC
,[t0].[CalendarYear]
ASC
,[t0].[EnglishDayNameOfWeek]
ASC
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!
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…
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.
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?
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).
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?
Can we pass run time parameters to store procedure ?
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.
Scott
Dynamic M parameters don’t work with MDX queries, which means they don’t work with Analyze in Excel unfortunately
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.
Thanks,
Deepa
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.
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.
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.
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.
Requirement:
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
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.
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
What error are you getting?
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”
Yes, although it’s not really the way classic Power BI reports work (paginated reports are the better choice here): https://blog.crossjoin.co.uk/2023/01/29/passing-any-arbitrary-value-from-a-power-bi-report-to-a-dynamic-m-parameter/
Hi chris, is it possible to get this working with multi select parameters. I get a error : we cannot convert a value type of list to to text type. See code below :
let
Source = Sql.Database(“LAPTOP-1P2FF402\MSSQLSERVER01”, “Sales Demo2020″, [CreateNavigationProperties=false]),
dbo_TESTFUNCTION = Source{[Schema=”dbo”,Item=”TESTFUNCTION”]}[Data],
#”Invoked Functiondbo_TESTFUNCTION1″ = dbo_TESTFUNCTION(Job, Company)
in
#”Invoked Functiondbo_TESTFUNCTION1″
You need to handle this in your M code – have you read this? https://blog.crossjoin.co.uk/2020/11/08/handling-multi-select-in-power-bi-dynamic-m-parameters/
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 (app.powerbi.com) 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?
What data source are you using? Can you publish a DQ dataset that doesn’t use dynamic M parameters that works?
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
@STATUSCUTOFF1 DECIMAL(2,2)
,@STATUSCUTOFF2 DECIMAL(2,2)
,@STATUSCUTOFF3 DECIMAL(2,2)
,@STATUSCUTOFF4 DECIMAL(2,2)
— Change cutoff parameters
,@CHANGECUTOFF1 DECIMAL(2,2)
,@CHANGECUTOFF2 DECIMAL(2,2)
,@CHANGECUTOFF3 DECIMAL(2,2)
,@CHANGECUTOFF4 DECIMAL(2,2)
)
RETURNS TABLE
AS
RETURN
(
SELECT ID
, PROPORTION
, PROPORTION_CHANGE
, CASE WHEN PROPORTION = 1 THEN 5
WHEN PROPORTION >= @STATUSCUTOFF4 THEN 5
WHEN PROPORTION = @STATUSCUTOFF3 THEN 4
WHEN PROPORTION = @STATUSCUTOFF2 THEN 3
WHEN PROPORTION = @STATUSCUTOFF1 THEN 2
ELSE 1
END AS STATUS
, CASE WHEN PROPORTION_CHANGE = 1 THEN 5
WHEN PROPORTION_CHANGE >= @CHANGECUTOFF4 THEN 5
WHEN PROPORTION_CHANGE = @CHANGECUTOFF3 THEN 4
WHEN PROPORTION_CHANGE = @CHANGECUTOFF2 THEN 3
WHEN PROPORTION_CHANGE = @CHANGECUTOFF1 THEN 2
ELSE 1
END AS CHANGE
FROM XXX.TABLEXYZ
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?
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.
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.
Hi there, when enabling multi select my visual is giving a error : Cannot convert a value of type list to to type text. How can i solve this , i would realy appreciate help