Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Date Slicer Based on a Dynamic Date Measure

I am trying to create a date slicer for my table that is pulling from one of 3 date columns, based on another slicer selection. 

 

I have a variance slicer that is choosing the data comparison that I want to see. Based on which one is picked, I want a second slicer that allows the user to select the year they want to analyze. 

 

There are 3 date columns in play here - if they select Intake or 90 Days to Closure in the variance slicer, it would pull the year options from the max_closure_date column, if they select the intake or 90 days to latest variance option, it would pull the max_assessment_date column, and if they selected intake to 90 days, it would pull from the max_90Day_date column. Not every record has all three dates, which is fine - the idea is to only pull records that have both required assessments on file and be filtered to the date of the secondary assessment.

 

 

I have a measure that is choosing which date column to pull from based on the variance slicer selection. It seems to be working fine if I pull its results into a table. The measure is created from this DAX: 

 

 

 

Date Filter (Selected) = 
// this variable is looking for the selected value from the disconnected slicer
// if nothing is selected, then display "No Variance Selected"
VAR VarianceSelected = SELECTEDVALUE('Variance Selection'[Variance], "No Variance Selected")
//Using IF statement to pass through the required values 
RETURN
IF(VarianceSelected = "Intake to Closure", YEAR(max('report v_ncfas_outcomes_statistics_data'[max_closure_date])), 
   IF(VarianceSelected = "Intake to 90 Days", YEAR(max('report v_ncfas_outcomes_statistics_data'[max_90Day_date])), 
      IF(VarianceSelected = "Intake to Latest", YEAR(max('report v_ncfas_outcomes_statistics_data'[max_assessment_date])), 
         IF(VarianceSelected = "90 Days to Closure", YEAR(max('report v_ncfas_outcomes_statistics_data'[max_closure_date])), 
            IF(VarianceSelected = "90 Days to Closure", YEAR(max('report v_ncfas_outcomes_statistics_data'[max_assessment_date])))))))

 

 

 

I can't use my Date Filter (Selected) field as a slicer since it is a measure. Any ideas how I could get a slicer to work based off of the correct years that are being returned from the Date FIlter (Selected) measure? I am trying to avoid having to do each option in its own workbook. 

Need Help

3 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Anonymous 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 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        AllisonKennedy
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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.