Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Pbik88
New Member

Dynamic Parameter not working using M query and slicer

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

1 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.

 

Pbik88_0-1668555948948.png

 thanks for all your help!

View solution in original post

5 REPLIES 5
Pbik88
New Member

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 :

 

Pbik88_0-1668538689164.png

 

amitchandak
Super User
Super User

@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...

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.

 

@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.

***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.

 

Pbik88_0-1668555948948.png

 thanks for all your help!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.