M Query parameters aren't connecting to my data slicers! Bind to Parameter?

[Anonymous]

I've spent a couple hours on a project today and I'm getting stuck on creating some date slicers for a Power Query I have in DirectQuery mode. I've seen a few people run into this but without a solution.

I'm connecting to an Oracle database and I have some M Query date parameters created for the user to select so that it can requery the database when these dates are changed. I have the dates split up into year, month, day so that there's the following parameters for a start and end date: startyear, startmonth, startday and endyear, endmonth,  endday. The datatype is set to text and accepts any value.

I created tables to go along with these so that I could have a slicer where users can change the dates from dropdown menus. 

the year tables have the years 2020-2024, the month tables have the values 1-12, and day has values 1-31. These all are text and not numerical.

As I've seen in multiple videos, you can bind the fields to your parameters from the relationships page. I've done this for each table connecting year, month, and day tables to their respective parameters.I then created the slicers, but ran into my issue here: When I select a value from my slicer, it doesn't update the value of the parameter. The videos I've seen, one of them from microsoft, show the values updating according to the selected value. I can just update the values from Transform Data > Edit Parameters, but I would like to have a visual so that this is a feature in the published dashboard.

A few things to note,

- the data types are matching, text values in the table to text values in the parameters

- the slicer is on the same page as the visuals it should be manipulating (I don't know if it would be an issue if they weren't since the slicers are supposed to change the query and not filter the visuals themselves)

- I also tried selecting the year, month, and day tables as the query for suggested values in the manage parameters window, but that didn't help either

Has anybody else had an issue like this or have an idea where I'm going wrong?

[Anonymous]

Hi @Anonymous ,

I checked your post, and my suggestion is to check whether the parameters in your M query are referenced correctly?

And there are some considerations and limitations to check out:

Considerations and limitations

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

For the parameter:
#date(2024, 7, 1) meta [IsParameterQuery=true, Type="Date", IsParameterQueryRequired=true]

for the db connection:

let
Source = Oracle.Database("10.206.10.58:1521/FITDBSBI", [HierarchicalNavigation=true, Query="


select * from equipment_history where posted > TO_DATE('" & Date.ToText(start_date, "MM/dd/yyyy") & "', 'MM/DD/YYYY')



"])
in
Source

[Anonymous]

Hi @Anonymous ,

You can check the relationships if they are active. At the same time, it is also necessary to pay attention to the flow of relationships. If table A flows to table B, the B field of table B is not filtered to table A.

Cross filter direction

Make this relationship active

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

The parameters are not for filtering already loaded data, they're just for storing date values that modify the query itself. The parameters are in power query, so as far as I can tell I can't edit that relationships outside of the power query window.

[lbendlin]

Start with a simple scenario. A single column , feeding a slicer, and bound to a parameter.  Make sure your Oracle query actually references that parameter.  Confirm that the query is rerun, and that you can see the modified data when the user interacts with the slicer.

Once that works you can then extend it to your scenario. Remember that each parameter can only be bound to a single column.

[Anonymous]

I have a table that's linked to my slicer, EndMonthTable. This is the m query for that table:

let
EndMonthTable = Table.FromList(
List.Transform({1..12}, each Text.PadStart(Text.From(_), 2, "0")),
Splitter.SplitByNothing(),
{"Month"}
)
in
EndMonthTable

to get months 01-12 formatted as text.

My parameter, endmonth, is text, and suggested values ar ea list of values 01-12.

My m query for data from my database is this:

let
Source = Oracle.Database("mydb", [HierarchicalNavigation=true, Query="


SELECT * FROM equipment_history WHERE posted > TO_DATE('" & endmonth & "/01/2024', 'MM/DD/YYYY HH24:MI:SS')

"])
in
Source

Just testing with one parameter, one table, one slicer, it still does not update the parameter value when I choose an option from the slicer and does not requery the database.

[lbendlin]

try 

let
Source = Oracle.Database("mydb", [HierarchicalNavigation=true, Query="
SELECT * FROM equipment_history WHERE posted > TO_DATE('" & endmonth & "/01/2024', 'MM/DD/YYYY')
"])
in
Source

Make sure this is in Direct Query mode, not in Import mode.

Did you bind the 'EndMonthTable'[Month] column to the "endmonth"  parameter?

[Anonymous]

Thanks for the suggestion, but the issue I'm having isn't with the m query itself. The query is effectively getting the value from the paramater and using it in the 'where' clause. The issue is with the EndMonthTable[Month] column and endmonth paramater:

I did bind 'EndMonthTable[Month] to endmonth on the relationships tab, but when I select a month value from my slicer (connected to EndMonthTable[Month]), this doesn't update the value of my endmonth parameter. So the bind is not working for some reason.

I also created this table to check the current values of my parameters, but no luck in trying to update them with slicers connected to column binds.

Is there some setting that I'm missing? Any other requirements for values to update via parameter binds and slicers?

[lbendlin]

is the parameter and the column of the same data type? Text?  It will be ambiguous - you should ues yearmonth instead.

[Anonymous]

Yes, they are the same data type.

what is yearmonth?

[lbendlin]

that's a column/parameter you don't have yet.  '202408'  for example.

Why not use the original start_date and end_date parameter?  do you have  a column for that in your data model?

[Anonymous]

I did previously use the start_date and end_date parameters, however I had the same issue. The reason I've switched to the day, month, and year parameters was for the sake of the slicers. Power BI doesnt currently support a slicer where I can pick a single date from a calendar, only a drop down list of dates which was not practical. Having day, month, and year in their own slicers means I only ever need to update the year table if i need to add more years, also makes it a little bit cleaner on the user side since they don't have to scroll through a long list of dates.

I'll try making a new parameter and column and see how that goes today. Lmk if you have any further suggestions in the meantime. Thanks!

[lbendlin]

Suggestion would be to start from scratch, with a single parameter and a small number of columns. Once you get that working as expected you can expand - step by step. Don't try to do too much at once.

[Anonymous]

Here's a simple parameter, value is "1" as a text data type. Table is a range of 1-10 but as text. Selecting from the slicer doesn't update the parameter value.

I also tried doing this in a brand new report, with nothing else besides the parameter and table, but then the "Bind to parameter" option doesn't even show up.

[lbendlin]

That's a good start.  Now try implementing the example from the article.

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

[Anonymous]

This also didn't work. Applied steps exactly as the article outlined. Slicers are still not updating parameter values.

[lbendlin]

Show the Power Query code?

[Anonymous]

for the db:
let
Source = Oracle.Database("10.206.10.58:1521/FITDBSBI", [HierarchicalNavigation=true, Query="


select * from equipment_history where posted > TO_DATE('" & Date.ToText(start_date, "MM/dd/yyyy") & "', 'MM/DD/YYYY')



"])
in
Source

for the parameter:
#date(2024, 7, 1) meta [IsParameterQuery=true, Type="Date", IsParameterQueryRequired=true]

[lbendlin]

That looks benign. And you can confirm that you connected in Direct Query mode?

[Anonymous]

yes, I'm in Direct Query mode

power bi version: Version: 2.132.1053.0 64-bit (August 2024)