Forum Discussion
How to create a Parameter to display data dynamically
Hi Kumar_0606,
Based on your requirement, the only reliable and supported approach for dynamic filtering like this is by using a Disconnected Date Parameter Table with Dynamic Measures.
Here the SELECTEDVALUE(ParameterDate[Date]) returns blank because the slicer uses a formatted MonthYear column that maps to multiple dates. This leads to multiple values being selected behind the scenes and SELECTEDVALUE does not return a value in such cases.
- So please create a seperate MonthSelector table with one row per month like for example 01 Jan 2025, 01 Feb 2025.
- Use the MonthYear column from this table in a single select slicer and update your DAX measures to use the selected MonthStart date from the MonthSelector table.
This will make sure accurate dynamic filtering using disconnected tables and give full flexibility to create CM, PM, PY flags in measures.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
Hi Kumar_0606,
Thank you for reaching out to Microsoft Fabric Community.
Thank you bhanu_gautam for the prompt response.
To filter data dynamically based on a selected date, please follow these steps:
- Create a Disconnected Parameter Table, go to Modeling --> New Table and enter this:
ParameterDate = CALENDAR(DATE(2023, 1, 1), TODAY())
This creates a date table that is not joined to your main data table. Do not create any relationships. Now add this ParameterDate[Date] column to a slicer on the report, so that to select a date.
- Create the Dynamic Measure for Current Month Amount:
Amount CM = VAR SelectedDate = SELECTEDVALUE(ParameterDate[Date])
RETURN
CALCULATE(
SUM('Table'[Amount]),
'Table'[Version] = "New",
YEAR('Table'[Date]) = YEAR(SelectedDate),
MONTH('Table'[Date]) = MONTH(SelectedDate)
)
This measure will return the total amount for the current month based on the selected date in the slicer.
Do not use calculated columns or Power Query parameters directly in DAX, because they are static and will not respond to slicer selections, so dynamic filtering will not work.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
Hello Anjan Kumar,
Thanks for the reply.
I created the ParameterDate table and the Measure Amount CM as you suggested.
When I change the ParamterDate in the slicer the table values are not changing accordingly.
Secondly how can i display ParamterDate like Jan 2025, Feb 2025. Mar 2025 etc in the slicer.
Hi Kumar_0606,
Check if the visual is using the Amount CM measure, only the visuals that use this measure will respond to the slicer. And also check if the slicer is using multiple selections and change it to select single mode.
To display the ParameterDate, add a new column to the ParameterDate table for example like below
MonthYear = FORMAT(ParameterDate[Date], "MMM YYYY")
And use this MonthYear column in the slicer instead of the raw date.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
Hello Anjan,
I am using only one visual here with Amount CM.
Also checked the slicer is Single select, not sure why the visual is not responding to the slicer.
Hi Kumar_0606
If the parameter isn't working as expected, you can definitely try using a normal slicer directly on the date field. With a regular slicer, you can select the date or time period you want to filter by, and the visual will respond accordingly.However, using a disconnected parameter is still the preferred method for more advanced dynamic filtering and comparisons (like comparing current and previous periods) without restricting the data.