Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Axis/ legend as a cumalative count

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:

Award Count = countrows(filter(all('All Award Data'),[Employee ID]=earlier(([Employee ID]))))
 



2019 & 2018 (all) slicer2019 & 2018 (all) slicer2018 slicer2018 slicer

 

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).

 

slicer on '2019'slicer on '2019'

1 ACCEPTED SOLUTION
tex628
Community Champion
Community Champion

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 🙂


Connect on LinkedIn

View solution in original post

3 REPLIES 3
tex628
Community Champion
Community Champion

Award Count = 
Var maxYear = MAX(Calendar[Year])
Return
countrows(filter(all('All Award Data'),
[Employee ID]=earlier(([Employee ID],
Calendar[Year] = maxYear
))))

Connect on LinkedIn
Anonymous
Not applicable

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('All Award Data'[Year])
Return
countrows(filter(all('All Award Data'),
[Employee ID]=earlier(([Employee ID],
'All Award Data'[Year] = maxYear
))))
 
The error I get is: Operator or expression '( )' is not supported in this context. What do you suggest?
 
Many thanks
tex628
Community Champion
Community Champion

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 🙂


Connect on LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.