Dynamic Parameter not working using M query and slicer

[Pbik88]

Hi,

I have watched several videos on creating a dynamic M query using Direct Query and a parameter and then updating the parameter through a slicer in the front end.

Although I followed all steps, including binding the parameter to the slicer , when I select a value from the dropdown in the slicer , it still takes the original default value of the parameter instead of updating it.

What could be the reason for this and is there a workaround for this issue? I am using Oracle SQL database

[Pbik88]

yes @amitchandak I am using the below condition :

TO_DATE('" & DateTime.ToText(DataDate) & "','YYYY-MM-DD HH:MI:SS AM')

I am not sure if there is anything wrong with this condition since it is picking the value from the parameter but not updating it from the slicer.

For eg. my default paramater value is "2022-10-18 12:00:00 AM" and below are the values I have added in my slicer :

[amitchandak]

@Pbik88 , Hope you are using Direct Query. Second check the list of supported databases

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters#considera...

[Pbik88]

Thanks @amitchandak , yes I am using Direct Query, also confirmed that this feature supports Oracle DB.

Below are the steps I performed :

1. Created a report using Direct Query to connect to my Oracle DB using a simple select statement with no where condition.

2. Created a Paramater of type Date Time and added a default value

3. Using Advanced Editor modified the M query to use the date filter from this paramater

4. Created a slicer with a list of Date Time values I wanted to filter my data on

5. Linked the date time column in the slicer to the paramater using Bind to Paramater under Advanced properties

6. Tried selecting different values from the dropdown in the slicer 

However, my report was generating data for only the date time value I manually entered in my paramater.

I am not sure where I am wrong since I am not getting any errors , it's just that the report does not refresh after changing the input date time from the slicer.

I would understand if the correct value would not get passed from the slicer or there was no data available for the particular date, but then the report should atleast have been empty to suggest that instead of showing data for the initially set paramater value.

[amitchandak]

@Pbik88 , Only additional thing I found in the document is this

If your parameter is of Date/Time data type, you will need to cast it within the M query as DateTime.Date(<YourDateParameter>)

I will try to check on my local env too.

[Pbik88 · Accepted solution]

***Solved***

I updated my PBID to the latest version and it's working now!

I think the older version let you create the dynamic M query using Oracle DB but did not work but the latest one does.

 thanks for all your help!