Default M-query parameter used to get data

[DCraneBMF]

Hello,

I've been struggling a bit with the M-query parameters. I created two date parameters (start/end) in the power query editor. 

In my report I created two datetables and bound these to the parameters. When I slice them everything works perfectly on my dataset and it filters them on the parameters set in the report. 

However, when I make a summarized table based on the filtered table I always get the results with the default/current parameters set in the power query-editor and not the parameters set in the report. Am I missing something? 

Thanks in advance. 

Regards,

Den

[DCraneBMF]

Hi @jbwtp,

Yes I'm following something like that. Not the same link, but something like that. 

I've tried a calculatetable, but the results is the same. As long as I keep the calculate functions within the actual table all is good. The moment I try to make a new one it uses the parameters set in the query-editor. 

Thank you for your help. I'm going to keep trying to see if I can solve it somehow. If I find out something I'll post it here. 

Regards,

Den

[jbwtp]

Hi Den,

I think the reason could be that you are creating a new table (via CREATETABLE or otherwise) and the filter is set on the original one. Because it is not a "true" PQ filter which is set at in a query and is dynamic and fold in the SQL query at runtime when the slicer/filter updates, I think, PBI engine just does not know that it exists when doing another table to whcih your slicer kind of does not apply. May by try explicitely forcing it via FILTER when doing SUMMARIZE or CREATETABLE?

Kind regards,

John   

[DCraneBMF]

Hi @jbwtp 

Thanks for your answer.

I'm still trying to figure out how the parameters work before I include them in my actual report.

Here's what I'm trying to do. 

I get my data from a SQL server, and I use "Direct query" to import the data. I want the user to be able to specify the period so that's where I want to use the query parameters for. To archieve this I did the following. 

I created a startdate and enddate parameter in the power query-editor. I assigned a current value to those parameters as you can see in the screenshot. 

I created a datetable to be used in a slicer. The datefield of that table I binded to the parameter I created earlier. 

Then in my report I created a table with the summerize function to get the number of records in the table. To my suprise the number of records (59 )in the summerized table are the number of records based on the values I included when I defined the parameters. 

The number of records in the actual table are based on the slicer parameters (31). Whenever I change the parameters I get a popup with the SQL query which shows the parameters I defined in the slicers. 

My expectation is that the number of records of both should be the same for both, because one is a summarized table of the other, but maybe I'm missing something or doing something wrong. 

Thanks in advance. 

[jbwtp]

Hi @DCraneBMF,

Thnaks for providing the details. I guess you are following something like: https://blog.crossjoin.co.uk/2022/02/20/tsql-table-valued-functions-and-dynamic-m-parameters-in-powe... 

Everything you do makes sense, I can't test it myself as I don't SQL (of for this reason any folding source). And from what I can see the dynamic query does return a correctnumberof lines,except for the Summarize function. Do you want to try any other function? I remember reading that SUMMARIZE behaves quite different to expectations in some scenarios. I suspect it may remove your filter and trigger re-run the query. Whatabout something like simple CALCULATE?

Kind regards,

John  

[jbwtp]

Hi @DCraneBMF,

This is a bit unclear what you are doing with the data. Would you be able to share some screenshots to illistrate your question? Do you set paramentes in M editor (in Power Query) and then load and use them in the report? Or you have different parameters in the report? How they relate to each other? Say if you have start/end date parameter earlier/later in the report comapred to Power Query, the data set will always be narrower than the parameters in the report would assume, etc.

Thnaks,

John