I have an employee table with a start date column and an end date column, and a "work" table with the employee id, a date and a count of hours. I also have a "Year" segment that allows me to filter the report by year.
I did this measure to calculate the number of employees in contract, who worked at least 5 hours during the selected year :
1. Create a 1-to-many unidirectional relationship between 'Person'[ID] and 'Work'[EmployeeID]. To avoid later confusion, I would recommend changing the name of Person[ID] to Person[EmployeeID]. You have a 'Work'[ID] column that seems to be an index column and not the Employee ID.
2. Set YearList[Year] in the rows of a matrix visual (or in the axis of any other type of chart)
3. Set this measure (based on your code) in values of the matrix:
which means that we have 7 hours for employee with ID 3 and 7 (3+4) hours for employee with ID 5. So that is two employees, which is what the measure returns. Both employees are also "in contract" in 2015 according to the Person table so, where is the issue?
OK. got it. The problem with that is that you want to use the same field in the slicer and in the rows of the matrix visual. Once you select one year in the slicer the matrix will be filtered and only that year will be shown in the matrix. I do not think you can override that behavior. I thought of a few alternatives:
1. Editing the interaction between the slicer and the matrix and eliminating it. This won't work because without the interaction we won't be able to harvest the value in the slicer through ALLSELECTED( ) or similar. Or so it appears.
2. How about simply using the 'Between' option in the slicer to select the years that you want shown?
3. You could also ditch the slicer and use the visual level filters of the matrix. Two filters on YearList[Year]:
a) One to show the years less than or equal to the year you would have selected in the slicer (2018 in your example)
b) Another one to show the top 5. So 2018-2014 will be shown
4. Lastly, you could create another one-column table, exactly the same as YearList, and use that one on the slicer (keep the current one on the rows of the matrix). Then you would avoid the issues described above. The measure would need some minor modification.