Forum Discussion
Date Slicer Based on a Dynamic Date Measure
AllisonKennedy Thank you for your response! The second slicer should be a list of years, and ideally would filter the view down to the year from the appropriate date column. For example, if they chose Intake to 90 Days in the first slicer, then the second date slicer would show the year options based on the max_90Day_column.
I have seen a lot of posts about creating a date series in a new table based off of a date measure, but it errors out when I do that, I think because it can't do the dynamic picking of the right date column. I'm new to DAX and power BI, so still trying to figure out the limitations.
I am wondering if doing it backwards might work - So creating a table of years, and then using the year field in that table to filter my measure?
Community ChampionAnonymous You will need to have a list of years that is in a table that is not filtered by the other slicer, then create a measure to filter it:
Filter Years =
VAR _YearsinPeriod = CALCULATE( VALUES ( DimDate[Year] ), FILTER(DimDate, CALCULATE(COUNTROWS(FactTable)) > 0 )
RETURN
IF ( SELECTEDVALUE ( YearSlicer[Year] ) IN _YearsinPeriod, 1 )
Then add that Filter Years as a visual level filter on your slicer that uses the YearSlicer[Year] column (unrelated to the other slicer) and then filter for Filter Years = 1.