Limiting a date slicer based off of another Date Slicer

[TBensen]

Hello,

In my dataset I have a Date column in an Incident table and another Date column in an Employee table that represent a Date of Incident and a Hire Date.  I'm using these columns for date slicers to show me the relative data for those dates.  I'm trying to limit the dates that are shown for the Hire Date slicer based off of the Date of Incident slicer.

For example if I set my Date of Incident to 1/1/2020 - 12/31/2020, I want to make the max date I can select on my Hire Date slicer to be 12/31/2020, effectively only showing me the population available up until the point of the Date of Incident.

EDIT:  I created a measure on my Incident Data table that calculates the Max date value when the Date of Incident slider is adjusted.

MaxIncidentDate = CALCULATE(MAX('Incident Table'[Date of Incident]), ALLSELECTED('Incident Table'[Date of Incident]))

Now that I have this measure created, I can capture the max date that is available in the "sliced" Incident table, but I'm struggling to relate that to the Employee table so I can set my max value to choose from for my Hire Date slicer.  What's the best way to get this to work?

Thank You,

Trevor Bensen

[TBensen · Accepted solution]

I was able to get this to function how I needed it to, albeit not exactley how I described I needed it to work.

On a table that is shared between my Incident table and Employee table, I created a measure that gets the Max Incident date from the Incident table like this:

MaxIncidentDate2 = MAXX(FILTER(ALLSELECTED('Incident Table'[Date of Incident]),'Incident Table'[Date of Incident]),'Incident Table'[Date of Incident])

Once I had this measure showing the date I needed that was based off of the selected Date of Incident, I created another measure on my Employee Table with some variables that returns a 1 or 0 based off of an IF statement.  Here is that Measure:

EmpMaxDateMeasureFilter =

VAR CurrentMaxValue = [MaxIncidentDate2]

VAR DateHiredValue = SELECTEDVALUE('Employee Table'[Date Hired])

Return

IF(DateHiredValue <= CurrentMaxValue, 1, 0)

For any visualizations that I needed to limit the Date Hired by the max value of the Date of Incident, I changed my Date Hired filter to be Filter Type = Top N, Show Items = Top 1, By Value = [EmpMaxDateMEasureFilter].  Now this filter allows me to select the Incident Date top value and changes the Employee values to only show/count any rows that = 1.  Going this route, I've eliminated the Date Hired slicer, because no one needs to select the Max Date Hired now.

Thanks,
Trevor Bensen

[v-stephen-msft]

Hi @TBensen ,

First you have to create a relationship between the two date columns.

Then based on my test, if the second slicer is of type list or drop down, it can display the date results filtered by the first slicer.

Therefore, after the first slicer filter, the dates after January 5, 2020 in Table 2 have been filtered out. The max date in Table 2 is January 5, 2020. All you have to do is to create a relationship between the two date columns.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TBensen]

Hello @v-stephen-msft,

Thank you for your reply.  I gave this a shot but there is another table in my data set that is preventing me from activating this relationship.  I have a seniority table that looks at how long an Employee has been with the company and groups them together, as well as some measures to calculate rates of incidents per Seniority group and the percentage of total incidents per seniority group.

This post is is related to this post: https://community.powerbi.com/t5/Desktop/Create-a-Counted-Summary-Table/m-p/1636873

The Incident Table and the Employee table are connected to this Seniority table, so if I try to relate the Employee and Incident table, I get an error.

Thanks again for your help!

Trevor Bensen

[amitchandak]

@TBensen , Usually we put a filter of measure in another slicer to control it. But I doubt it works on the range. https://www.youtube.com/watch?v=cyOquvfhzNM

[TBensen]

Is there a way to use the max value of the Date of Incident in the same manner as you are using the measure in this video? If we can capture the max value of the Date of Incident and use that as the filter for the Hire Date, I think that would do the trick, right?

Thanks,
Trevor Bensen