Forum Discussion
Date Slicer Based on a Dynamic Date Measure
Community ChampionAnonymous I don't fully understand what you want the second slicer to show, but you can use a slicer as a visual level filter to narrow the values that are displayed on that slicer. A slicer cannot be used as a filter (as you know), but if you create it as a column then it won't know what selection was made in the previous slicer because of order of operations of calculations in Power BI:
https://excelwithallison.blogspot.com/2020/09/reporting-order-of-operations.html
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?
- AllisonKennedy
Anonymous 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.