Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I am currently tracking number of awards given to employees. I am having a problem slicing this data as 2018 or 2019 or both.
I do this setting the value as Employee ID code by 'Count' and the legend as a calculated measure called which summarises the different counts that individual employees have been awarded (e.g. over the time period been awarded 1, 2, 3, 4 etc. awards).
Measure:
The problem I am having is now that 2019 is here, when I use a slicer on year (2018 or 2019), for 2019 it doesn't look at just those awarded in 2019. The measure still refers to both 2018 and 2019 data (when 2019 selected, it should appear as 13 employees have each received 1 award, rather than referring to 2018 data also).
Solved! Go to Solution.
Award Count = Var maxYear = MAX(Calendar[Year]) Return Calculate( Countrows('All Award Data'), All('All Award Data'), [Employee ID]=earlier(([Employee ID], Calendar[Year] = maxYear ))))
Try this 🙂
Award Count = Var maxYear = MAX(Calendar[Year]) Return countrows(filter(all('All Award Data'), [Employee ID]=earlier(([Employee ID], Calendar[Year] = maxYear ))))
Hi @tex628, I have tried your solution but appear to be getting an error.
In my dataset I am not using a seperate calendar table, rather I have a year column within 'All Award data'
I used this (taken from your suggestion):
Award Count = Var maxYear = MAX(Calendar[Year]) Return Calculate( Countrows('All Award Data'), All('All Award Data'), [Employee ID]=earlier(([Employee ID], Calendar[Year] = maxYear ))))
Try this 🙂
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |