Author: Luc Debois © July 2025
Situation
How to create a dynamic visual where the years and values change depending on the choice in a slicer.
The above number of employees per ‘Date of Birth’ & ‘Hire Date’ must be shown on 1 ‘Stacked Column Chart’.
Available:
Table: HR Data with EmployeeID – FirstName – LastName – DateOfBirth – HireDate – …
Procedure
Step 1 – Creation of Calendar Date Table
This Calendar Table is based on the ‘lowest’ and ‘highest’ value of the DateOfBirth and HireDate
We added also a column of the Year and Month.
Step 2 – Model View
Create the relationships between:
-/ Date and DateOfBirth <= Active relationship
-/ Date and HireDate <= Not Active relationship
Step 3 – Create an additional Table CalendarStartSelector
This Table contains 1 column with 2 values: “Date of Birth” and “Hire Date”
 | => |  |
Step 4 – Create the following measures
=> SelectedStartDate <=
Based on the selected value in the Slicer (See further) the measure will look up the lowest value of the DateOfBirth or HireDate.
=> ShowData <=
Based on the value of the measure ‘SelectedStartDate’ it will filter the dates(years) above the min value.
=> CountEmployeesBD <=
Count the Employees bas on the relation ‘DateOfBirth’
=> CountemployeesHD <=
Count the Employees bas on the relation ‘HireDate’
=> CountEmployeesSelection <=
Step 5 – Creation of Visuals
Step 5.1 – Create a Slicer
Select ‘StartType’ of the Table CalendarStartSelector add this to a Slicer – Visual
Step 5.2 – Create a Stacked Column Chart
– / Add ‘Year’ to the X- Axis
-/ Add ‘CountEmployeesSelection’ to the Y-Axis
-/ Add ‘ShowData’ to Filter of the Visual, and filter on the value 1
Step 6 – Change the Title of the Stacked Visual Chart
Step 6.1 – Create measure
=> ChartTitle <=
Creates a text based on the selected value in the Slicer
Step 6.2 – Add Measure to the Visual
Goto ‘Format your Visual’ – General – Title
Choose for ‘Conditional formatting
Add the measure to the Conditional Formatting dialog box by selecting ‘Field value’ for ‘Format Style and ‘ChartTitle’ for ‘What field should we base this on?’
Now the Title of Visual is changing depending on the Choice of the date
Result
Conclusion
3 things are important to solve this problem:
- Dedicated Calendar table where the contents will be created based on BirthDate and HireDate (lowest and highest value).
- The USERELATIONSHIP switches between the two relationships BirthDate and HireDate.
- The ‘Showdata’ measure is used as a filter which displays only the necessary years on the Chart.
Note
Additional credits are given to Henk Vlootman and Štěpán Rešl for providing guidance.